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'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 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
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)
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.
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
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!!
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:
