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)
Why don't i ever get return value of 1 when the following binary column (profSignature) is null? RETURN SELECT ISNULL(profSignature, profSignature) FROM mpProfiles WHERE ApplicantID = CAST(@CID AS INT) AND ProfileID = CAST(@PID AS INT)
I have a problem with a database in SQL Server 2000 8.00.194. I have read similar cases in the forum, but in all the cases the expert paulrandal requests sent it a detail of dbcc page (). By mistake the user try to restore a backup on this database, the user realized the error and press the "stop" button of the SQL Server, the process stopped but the database is not recognized from SQL Server 2000. I have opened the file. mdf and the previous data are inside. I attach the database in "DBO Use Only" state using the tool "REBUILD_LOG" when I execute DBCC CHECKDB ('<db_name>') I have the same symptoms that in the post
Attemp to fecth logical page (1:8945) in data base belong to object 'sysindexes', not to object 'syscolumns'
But I don't understand like it has been solved finally.
Does is it possible to recover these data?. It is not obligatory it recovered all the database, only some tables to complete manually on to backup of this database. I try BCP, but I have the same error
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?
I havethe following query which returns one row of data, however, the MedicalcodeID is NULL.
SELECT db1.dbo.Referral.ReferralGuidDigest, dbo.patient.PatientID, dbo.Consultation.ConsultationID, dbo.Staff.StaffID, db1.dbo.Referral.EffectiveDateTime AS EventDate, db1.dbo.Referral.Status AS ReferralStatus, db1.dbo.Referral.Mode AS ReferralMode, db1.dbo.Referral.ServiceType, db1.dbo.Referral.Urgency, db1.dbo.Referral.Direction, db1.dbo.Referral.Transport, db1.dbo.Referral.EndedDate, db1.dbo.Referral.ReceivedDate, dbo.lkupMedical.MedicalCodeID, db1.dbo.Referral.Term,
[code]...
It is clear from teh above - that the expected MedicalCodeID = 33959 and NOT NULL. I dont understand why SQL added the COLLATE SQL_Latin1_General_CP1_CS_AS to the query - am working on a database developed by another person. Could it be the ACode and ReadCode in dbo.lkupMedical is not set up with SQL_Latin1_General_CP1_CS_AS. How to implement to LkupMedical table....
I changed HIGHLIGHTED JOIN to Inner/Right but it never yielded any results, no record found..
i have the following query to sum the total due balance for a customer:
select sum(outstanding)from out where customer = 'myvariable' the problem is when the customer has no outstanding it returns NULL is there a way to return 0 when there are no outstanding?
I am trying to script out the creation of database scripts. I am tryingto use @@servername in the statement. I found out the select@@servername returns NULL. I used sp_dropserver to drop any servernamesfirst, restarted SQL, ran sp_addserver 'servername' to add theservername, restarted SQL. select @@servername still returns NULL...Any ideas why this may be happening?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I am using the following C# code and T-SQL to get result object from aSQL Server database. When my application runs, the ExecuteScalarreturns "10/24/2006 2:00:00 PM" if inserting a duplicated record. Itreturns null for all other conditions. Does anyone know why? Doesanyone know how to get the output value? Thanks.------ C# -----aryParams = {'10/24/2006 2pm', '10/26/2006 3pm', 2821077, null};object oRtnObject = null;StoredProcCommandWrapper =myDb.GetStoredProcCommandWrapper(strStoredProcName ,aryParams);oRtnObject = myDb.ExecuteScalar(StoredProcCommandWrapper);------ T-SQL ---ALTER PROCEDURE [dbo].[procmyCalendarInsert]@pBegin datetime,@pEnd datetime,@pUserId int,@pOutput varchar(200) outputASBEGINSET NOCOUNT ON;select * from myCalendarwhere beginTime >= @pBegin and endTime <= @pEnd and userId = @pUserIdif @@rowcount <0beginprint 'Path 1'set @pOutput = 'Duplicated reservation'select @pOutput as 'Result'return -1endelsebeginprint 'Path 2'-- check if upperlimit (2) is reachedselect rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))),count(rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))))from myCalendargroup by rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30)))having count(rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30)))) =2and (rtrim(cast(beginTime as varchar(30))) + ', ' +rtrim(cast(endTime as varchar(30))) =rtrim(cast(@pBegin as varchar(20)))+ ', ' + rtrim(cast(@pEnd asvarchar(20))))-- If the @@rowcount is not equal to 0 then-- at the time between @pBegin and @pEnd the maximum count of 2 isreachedif @@rowcount <0beginprint 'Path 3'set @pOutput = '2 reservations are already taken for the hours'select @pOutput as 'Result'return -1endelsebeginprint 'Path 4'--safe to insertinsert dbo.myCalendar(beginTime, endTime,userId)values (@pBegin, @pEnd, @pUserId)if @@error = 0beginprint 'Path 4:1 @@error=' + cast(@@error as varchar(1))print 'Path 4:1 @@rowcount=' + cast(@@rowcount as varchar(1))set @pOutput = 'Reservation succeeded'select @pOutput as 'Result'return 0endelsebeginprint 'Path 4:2 @@rowcount=' + cast(@@rowcount as varchar(1))set @pOutput = 'Failed to make reservation'select @pOutput as 'Result'return -1endendendEND
I have the following code in my custom source component's AcquireConnection method -
if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)
{
ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);
ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;
if (cmado == null)
throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");
// Get the underlying connection object. this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;
if (oledbConnection == null)
throw new Exception("The ConnectionManager is not an ADO.NET connection.");
isConnected = true;
}
The value of oledbConnection is null.
I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?
ConnectionManager cm = DtsConvert.ToConnectionManager(conMgr);
Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet cmado = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.ConnectionManagerAdoNet;
OleDbConnection conn= cmado.AcquireConnection(null) as OleDbConnection;
}
}
In the sample above the conn is null.
To this query Darren replied to use the connection of type ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 to create the connection.
Your call to GetConnectionsOfType("OLEDB"); will return native OLE-DB connections, not ADO.NET connections, using the OleDbConnection.
The connection type for the ADo>NET OLE-DB connection is -
but if user creates an New OLEDB Connection from the Connection Manager panel of the BIDS and selects the same from the custom UI how to get the underlying OLEDBConnection? the CreationName in this case is "OLEDB" and not ADO.NET:System.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089.
I find it weird when decrypting a column from a baked up database and restoring it to another database. Here's the scenario:
Server1 has Database1. Database1 has Table1 with two columns encyrpted -- Card Number and SS Number Encryption and decryption in this Database1 is perfectly fine. Records are encrypted and can be decrypted too. Now, I tried to backup this Database1 and restore it to another server with SQL 2005 instance called Server2. Of course the columns Card and SS Numbers were encrypted. I tried decrypting the columns using the same command to decrypt in Database1, however, it returns a NULL value
Here's exactly what I did to create the encyprtion and decryption keys on the restored database: -- Create the master key encryption CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myMasterPassword'
-- Create a symetric key CREATE SYMMETRIC KEY myKey WITH ALGORITHM = DES ENCRYPTION BY Password='myPassword'; Go
-- Create Card Certificate CREATE CERTIFICATE myCert WITH SUBJECT = 'My Certificate on this Server'; GO
-- Change symmetric key OPEN SYMMETRIC KEY myKey DECRYPTION BY PASSWORD = 'myPassword';
-- I then verified if the key is opened SELECT * FROM sys.openkeys
If I create a new database, say Database2 from that Server2, create table, master key, certificate, and symmetric key. Encrpytion and decryption on Database2 will work!
Any suggestions gurus? I tried all searches and help for almost 2 weeks regarding this issue but nobody could resolve this.
I have a SQL 2005 clustered server which is returning a Null value for @@servername. I find the server entry in sysservers. I have replication configured on this so i am not able to do a Sp_dropserver & sp_addserver as this acts as a publisher. The configured merge repication stopped working because of this issue and I am not able to delete replication as the the delete option uses @@servername which returns a null value. So I am struck in a loop.
Hi folks,I have an issue with a column I created in my query called Instance.SELECT Object_Id, Event_type, Audience, Subject, Provider, Academic_Year, Start_date, End_date, CONVERT(varchar, Academic_Year) + ' (' + CONVERT(varchar, Start_date, 103) + ') : ' + Event_type AS InstanceFROM EventsORDER BY Event_type Above is my query. The problem is because the start date column can be null, it also returns the Instance column as null for that row.I thought it would have just missed out the date and display the rest, but it doesn't.Is there any way I could get the Instance column to display a value, when the start date is null?ThanksEdit: Managed to sort it using ISNULL()
I have a built an application to manage the report-subscription on my ReportServer. I'm having some troubles with retrieving the subscription properties.
My problem: I can retrieve all my propertie except the schedule definition, when I deserialize it, the Sceduledefenition.Item always returns null.
I use the next code to deserialize it: ////////////////////////////////////////////////////////////////////////////////////////////////////// private XmlAttributeOverrides GetSchedule() { XmlAttributeOverrides xmlAttrOverride = new XmlAttributeOverrides(); XmlAttributes XmlAttr = new XmlAttributes(); XmlAttr.Xmlns = false; xmlAttrOverride.Add(typeof(ScheduleDefinition), XmlAttr); xmlAttrOverride.Add(typeof(MinuteRecurrence), XmlAttr); xmlAttrOverride.Add(typeof(DailyRecurrence), XmlAttr); xmlAttrOverride.Add(typeof(WeeklyRecurrence), XmlAttr); xmlAttrOverride.Add(typeof(MonthlyRecurrence), XmlAttr); xmlAttrOverride.Add(typeof(MonthlyDOWRecurrence), XmlAttr); xmlAttrOverride.Add(typeof(DaysOfWeekSelector), XmlAttr); xmlAttrOverride.Add(typeof(MonthsOfYearSelector), XmlAttr); return xmlAttrOverride; }
private ScheduleDefinition DeserializeScheduleDefenition(string MatchData) { XmlAttributeOverrides xmlAttrOverride = GetSchedule(); XmlSerializer ser = new XmlSerializer(typeof(ScheduleDefinition),xmlAttrOverride); Stream stream = new MemoryStream(System.Text.Encoding.Default.GetBytes(MatchData)); stream.Position = 0; return (ScheduleDefinition)ser.Deserialize(stream); } ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I'have tried almost everything, but could not find the solution.
Please, this is very important for me, so every tip or shared thought could be very helpfull.
I am hoping someone could help me understand why this is happening and perhaps a solution. I am using ASP.NET 2.0 with a SQL 2005 database. In code behind, I am performing a query using a parameter as below: sql = "SELECT field_name FROM myTable WHERE (field_name = @P1)" objCommand.Parameters.Add(New SqlParameter("@P1", TextBox1.Text)) The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query. The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way: sql = String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text) I also get NO records retuned using the same criteria. What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning? Thanks!
If I query sql server I get 10 results. But when I use if (myReader.Read()) I get only 7 results. I found that there was a Null field in the DB. I changed it and it worked. The problem is I don't want to touch the database and set all null fields. There must be a way to get all results including the Null using sqlDataReader so that if (myReader.Read()) is used it does the right comparison.
// This code is called 10 times with a select * from where item="xxx" P21Conn.Open();
I have code that has worked just fine for some time, and now all of the sudden I am having an issue. I have a simple INSERT statement built and then make the following call:
RecordID = cmd.ExecuteScalar
I have never had a problem with this before. The RecordID of the newly inserted record is returned into the RecordID Integer varibale. All of the sudden, the varibale has a value of 0 (null I assume is being returned), but yet the INSERT worked just fine. I can check the table in SQL and it is populated with no issues.
No exception is thrown of any type or anything. Does anybody know what may be happening?
The DecryptByKey function occasionally returns null even though the EncryptByKey function retuned a non-null value. The problem only occurs for a subset of rows returned by a single select and every time the script is executed, a different set of rows is affected by the problem. Occasionally all fields get encrypted/decrypted successfully, but this is rare.
It seems that the EncryptByKey function occasionally returns a value that can not be decrypted at a later point in time.
I am running on Windows XP Professional SP 2 with SQL Server 9.0.3042.
I have included a sample of the code below.
Thank you, Mike
CREATE FUNCTION [dbo].[encrypt_text] ( @input_text varchar(255) ) RETURNS varbinary(8000) AS BEGIN RETURN EncryptByKey(Key_GUID('eia_key'), @input_text) END
CREATE FUNCTION decrypt_text ( @input_text varbinary(8000) ) RETURNS varchar(255) AS BEGIN return convert(varchar(255),DecryptByKey(@input_text))
END
IF EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = N'eia_key')
DROP SYMMETRIC KEY eia_key
CREATE SYMMETRIC KEY eia_key
WITH ALGORITHM = DES
ENCRYPTION BY PASSWORD = '???'
OPEN SYMMETRIC KEY eia_key DECRYPTION BY PASSWORD = '???'
execute util_print 'Deleting data'
execute ld_delete_lips_data
execute util_print 'Loading data'
set nocount on
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (1, 'TERM', 0, 0)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (2, '0 - 2', 0, 2)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (3, '2 - 5', 2, 5)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (4, '5 - 10', 5, 10)
insert into maturities (maturity_id, maturity_name, minimum_maturity, maximum_maturity)
values (5, '10+', 10, null)
insert into forecast_horizons (forecast_horizon_id, forecast_horizon_name, forecast_horizon_alias)
Is there a way to check for System.BDNull when I use the column name instead of column index? The column Photographer (in the excample below) sometimes contains the value null, and then it throws an error. Or do I have to go back to counting columns (column index of the returned data) again? try { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read()) { albumItem = new Album((int)reader["Id"]); if (reader["Photographer"] != null) albumItem.Photographer = (string)reader["Photographer"]; albumItem.Public = (bool)reader["IsPublic"]; } } } Regards, Sigurd
I have a stored procedure like "select * from ThisTable" I'm doing a dataread like: Dim val as String = dateRead("column_from_ThisTable") If the value in the column is not null everything works great, but if the value is null, instead of getting a value of "" which I expect, I get the column name??? In this case "column_from_ThisTable" How do I make sure I get "" returned when the value in the column is db.null?
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
Hi all I am having some issues in selecting items from my database where the record is NOT NULL. I have the code below however although some fields do contain soem data in it, others are blank which I believe are empty spaces. How do I do a SELECT command which ignores empty spaces and NULLS?
Code Snippet
SELECT CustomSearch FROM OfficesTable WHERE CustomSearch IS NOT NULL Thanks, Onam.
I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.
I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.
Does anyone know how I can elegantly get around this problem other than my best guess below:
Best guess pseudo code: IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.
Here's some code that reconstructs the problem: select datalength(char(13)) CarriageReturnVisible , datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]
Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable. Is it possible using DataTable with some null values in it? Thanks
Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.
for the query i created i need zeros where ever the filed is blank. i have used count(acc) for selecting the count . can any one help me out with sample query. Thanks in advance
I have two tables, tblMTO and tblIMPORT_MTO. If I import an entire MTOinto the import table I want to create a delta from it (i.e. leave onlythe changed items). I have a view (simplified)SELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,dbo.tblIMPORT_MTO.Rev AS New_Rev, dbo.tblMTO.Rev AS Old_RevFROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONoNow to get all rows where old_rev = new_rev I also want all rows whereboth are null, is the best method to put ISNULL() in the view or toselect from the view using ISNULL in the criteria, e.g.select * from view1 where ISNULL(Old_Rev,0)=ISNULL(new_rev,0)or in the viewCREATE VIEW view1 asSELECT dbo.tblIMPORT_MTO.ImportID, dbo.tblIMPORT_MTO.MTONo,ISNULL(dbo.tblIMPORT_MTO.Rev,0) AS New_Rev, ISNULL(dbo.tblMTO.Rev ASOld_Rev,0)FROM dbo.tblIMPORT_MTO LEFT OUTER JOINdbo.tblMTO ON dbo.tblIMPORT_MTO.MTONo = dbo.tblMTO.MTONo;select * from view1 where Old_Rev=new_rev;
does sql server mobile 2005 support the isnull function? I'm getting an error when I try to use it and I don't know if it is becuase of using the isnull function or not, but when I run the same query on Sql Server 2005 it works fine.
