i have 2 stored procedures: a delete and a select. the delete sp returns the rowcount properly. the select returns null. the code for both sp's is extremely simple and extremely similar. when i execute the select sp in server management studio the rowcount shows a 1 as expected. but the calling method gets null.
SP Code
ALTER PROCEDURE [dbo].[RetrieveEmployeeKeyFromAssignmentTable]
@assignmentPrimaryKey int,
@rowCount int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT employeePrimaryKey FROM assignmentTable WHERE primaryKey = @assignmentPrimaryKey;
SET @rowCount = @@RowCount;
END
c# code
SqlConnection conn = GetOpenSqlConnection();
if (conn == null) return true;
SqlDataReader reader = null;
SqlParameter p1 = new SqlParameter(); SqlParameter p2 = new SqlParameter();
I have a vb.net application that executes a simple flat file to sql table dtsx package. I want to capture the rowcount to display back to the user to verify the number of rows that were inserted or updated to the table. I have a Row Count component placed between the flat file source(without errors) and the destination component. I have assigned a variable named RecordCount to the Row Count component. So far so good I hope : )
Now, I also use a variable to "feed" the package the flat file source. This works fine, but I cannot figure out how to retrieve the row count information and how to assign that to the variable RecordCount.
Also, if anyone has any insight on the way to work with the OnProgress method in SSIS I would appreciate that as well. In SQL 2000 using DTS I create a "PackageEventsSink" that I had found online, and it worked great for monitoring the progress of the DTS. Can't seem to figure out how to get it to work in SSIS.
SQL Server 2000 SP3.Is it possible for the @@ROWCOUNT function to return NULL after astatement? I am troubleshooting a relatively large stored procedure withmultiple SELECT statements and a couple of INSERTs into table variables.Immediately after each statement I save the value returned by @@ROWCOUNT toa local variable. That information eventually is passed back to the clientvia one output parameter, for all statements in the procedure.Occasionally, the value returned via that parameter is NULL. This cannot bereproduced by re-running the SP with the same input parameters.Before doing any further troubleshooting, I would like to rule out thepossibility that @@ROWCOUNT can actually return a NULL under somecircumstances. From searching the archives, it appears that in SQL Server7.0 this could happen in the context of a DML query on a table withtriggers. This is not the case here - the only DML queries are INSERTs intotable variables, all other queries in the SP are SELECTs.Any related information would be appreciated.--remove a 9 to reply by email
The code below returns the members who have accounts that are inactive, and works correctly. It returns the memberid, but returns null values for the barcode values. I am explicitly looking for unmatched rows, i.e. the LEFT OUTER JOIN will return joined member-address rows which do not have a matching account, according to the ON conditions, and these are the ones I want because of that WHERE condition. Therefore account.barcode will always be NULL too.
Is there to perform the same query and return account details? I have tried running a query to return just the memberid values and then perform a second query to match the account details, but this is very ineffecient and slow.
select member.memberid, account.barcode FROM member INNER JOIN address ON address.MemberID = member.Memberid LEFT OUTER JOIN account ON account.Memberid = member.MemberID AND account.enddate >= current_date AND account.closed = 0 WHERE account.Memberid is null AND member.isDeleted = 0
I've got the following query in SQL 2000:select a.SSN, a.MonthName, a.IMClinicDay,b.IMClinicDay as SecondDayfrom tblResidentRotations ainner join view7 bon a.SSN = b.SSNwhere a.AcademicYear = '2004-2005' and a.SSN = '999999999' anddatename(month, a.IMClinicDateFirst) = b.MonthNameThis query returns a resultset like this:<SSN> <Month> <a.IMClinicDay> <SecondDay>999999999 July Friday PM Tuesday PM999999999 September Tuesday PM Friday PM999999999 October None Friday PM999999999 November Friday PM Tuesday PM999999999 January Tuesday PM Friday PM999999999 April Friday PM Monday PM....and so onFor some of the months, there is a null value for "b.IMClinicDay". Forexample, it's null for August, December, and February. I want myresultset to look like this:<SSN> <Month> <a.IMClinicDay> <SecondDay>999999999 July Friday PM Tuesday PM999999999 August Tuesday PM null999999999 September Tuesday PM Friday PM999999999 October None Friday PM999999999 November Friday PM Tuesday PM999999999 December Tuesday PM null999999999 January Tuesday PM Friday PM999999999 February Friday PM null999999999 April Friday PM Monday PM....and so onHow can I return a null for these days? Thanks for any help oradvice.
I have a simple table on my webpage which shows data from my database. It all worked fine apart from one part. I wanted the select statement to select only NULL values from a column, as these are classed as open queries. Once I have closed the query a 0 or 1 will automatically be posted and will no longer have a NULL value. I have a simple select statement (which I will post on here when I log in with my work computer) and at the end I have a WHERE Column = NULL. I have also tried WHERE column <> 0.0 AND column <>1.0 and that did not work. If I only did WHERE column <> 1.0, i only get the 0.0 results and none of the NULL results, but if I have no WHERE statement I get all the results including the NULL values.
I have a table tblnetwork on which identity property is defined on column networkid.
When I issue the following command new record is getting inserted into tblnetwork and identity column is getting incremented properly. But, I am not able to get the @@identity value It is returned as NULL.
Using SQL2000. I want to return the # of columns with non-nullvalues. Here's my query so far:selectcase when Dx1 is not null then 0 else 1 end +case when Dx2 is not null then 0 else 1 end +case when Dx3 is not null then 0 else 1 end +case when Dx4 is not null then 0 else 1 end as DxCountfrom tblClerkshipDataCleanwhere PalmName = @PalmNameThere are 7 rows for the particular PalmName I'm using. The queryreturns a result of 7. However, there are 25 values in Dx1 thru Dx4so the query should be returning 25.What am I doing wrong here? Thanks in advance.
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.
I am trying to insert a record to based on the source below, however the GUID of GiftOcc_ID is being returned as zero's so the first record can be added but as it is defined as the primary Key and uniqueidentifier the next record fails with a duplicate entry. Basically how do I ensure that the GUID is created and not nulls. As you can see I am trying to use Newid() which I have inserted as a default value but that does not work. Also as it is a unique identifier the "is identity" is not available
Protected Sub CreateGiftOccasion(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click Try Dim null As New Nullable(Of Integer) Dim da As New DataSet2TableAdapters.Gift_OccasionTableAdapter Dim GiftOcc_ID As Guid da.Insert(newid(), Occ_Type_Text.Text, Occ_Desc_Text.Text, Calendar1.SelectedDate, Calendar2.SelectedDate, 1) Catch ex As Exception
I'm still having issues with this despite my attempts to resolve. I even have "with exec as dbo" in my sproc, and and "exec as dbo" in my execution, but still the encrypted data returns nulls when I exec as a user other than DBO. Below is precisely what I have done. All ideas are welcomed.
TIA, ChrisR
--If there is no master key, create one now
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJKL95QYV4369#ghf0%94467GRdkjuw54ie5y01478d Dkjdahflkujaslekjg5k3fd117 r$$#1946kcj$n44ncjhdlj' GO
CREATE CERTIFICATE HumanResources037 WITH SUBJECT = 'Employee Social Security Numbers'; GO
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = DES ENCRYPTION BY CERTIFICATE HumanResources037; GO
USE [AdventureWorks]; GO
-- Create a column in which to store the encrypted data ALTER TABLE HumanResources.Employee ADD EncryptedNationalIDNumber varbinary(128); GO
-- Open the symmetric key with which to encrypt the data OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037;
-- Encrypt the value in column NationalIDNumber with symmetric -- key SSN_Key_01. Save the result in column EncryptedNationalIDNumber. UPDATE HumanResources.Employee SET EncryptedNationalIDNumber = EncryptByKey(Key_GUID('SSN_Key_01'), NationalIDNumber); GO
-- Verify the encryption. -- First, open the symmetric key with which to decrypt the data OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE HumanResources037; GO
-- Now list the original ID, the encrypted ID, and the -- decrypted ciphertext. If the decryption worked, the original -- and the decrypted ID will match.
alter procedure getDecryptedIDNumber with exec as owner as SELECT NationalIDNumber, EncryptedNationalIDNumber AS "Encrypted ID Number", CONVERT(nvarchar, DecryptByKey(EncryptedNationalIDNumber)) AS "Decrypted ID Number" FROM HumanResources.Employee; GO
/*works for me, shows the decrypted data*/
exec getDecryptedIDNumber
USE [master] GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[AdventureWorks], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
USE [AdventureWorks] GO
CREATE USER [test] FOR LOGIN [test] GO
use [AdventureWorks] GO
GRANT EXECUTE ON [dbo].[getDecryptedIDNumber] TO [test] GO
GRANT IMPERSONATE ON USER:: dbo TO test; GO
/*Now, open up a "file/new/DB Engine Query" and login with the test login*/ exec as user = 'dbo' exec getDecryptedIDNumber
/*This returns NULL values where it should show the decrypted data*/
Hello again! I'm having yet another problem with my code.
CASE Kit WHEN 'Y' THEN '(Kit)' WHEN 'N' THEN '' END + ' ' + CASE cream WHEN 'Y' THEN '(cream)' WHEN ' N' THEN '' END + ' ' + CASE Phone WHEN 'Y' THEN '(Phone)' WHEN 'N' THEN '' END
The problem that I am running into is that if one of the values comes up as NULL the whole field is NULL. am i doing something wrong?
I've a package that has a excel source. But i'm having a strange problem with it. One of the columns in the source file have a lot of null values but not all of them. But when i run the package a put a data viewer right after the source and i can see that it's showing that the few fields that should have values are also null. I've tried a lot of things but they didn't work. I need some help and fast if possible. Example: Source file.xls Name Grade OtherGrade John 30 30.23 In the DataViewer Name Grade OtherGrade John 30 NULL
Hi there ; This Problem is goin to make me crazy! I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 . I used asp.net 2.0 to insert datetime values, directly, using sth like DateTime.Now.ToString() . i use 2 selects as follows : 1)select * from X where Date1='8/9/2006 11:17:41 AM' 2)select * from X where Date2='8/9/2006 11:17:41 AM' #1 is OK, but the second one returns a row with all columns set to NULL . (X is the name of the table) Thanks in advance
I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration. Thank you in advnace for any help on this! INSERTrequest ( emp_id, request_submit_date, request_start_date, request_end_date, request_duration, request_notes, time_off_id ) Select@emp_id, GETDATE(), @request_start_date, @request_end_date, 1 + DATEDIFF(day, @request_start_date, @request_end_date) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @request_start_date and @request_end_date), @request_notes, @time_off_id
i have a stored procedure that has parameters. this dataset is used in a crystal report. the parameters are in the sp and will return records if a value is selected. i would like to return records if one parameter or all parameters are selected. there are 3 parameters, date range, receipt, po # if i select a distinct value and leave the others null, or 2 values 1 null etc, i would like to return the records, i am having trouble with the syntax. thank you
We currently have an SQL db running on a web server.One of these fields is a large(ish) amount of text data – up to 400characters – and has been cast variously as varchar, nchar and texttype to overcome a problem. The problem appears to be in retrievingthe data via ASP. I understand that ASP can handle string data of thissize so I am okay there.When the records are retrieved from the db, the data string length =0.I know the data is there because I have written a Delphi data managerwhich interrogates the db and shows all records and their contents.So if ASP can handle strings this size and the data is there, why do Iget a data length of zero bytes returned when I interrogate the recordset?Whichever way I cast this field I get the same result.I know the code is sound as it works locally through a MS SQL serveron my PS.Anyone have this problem or know what's causing it? I have logged asupport call with my hosting company, but they haven't replied as yetand I am stuck on an urgent project.Any suggestions?CheersGrant
I'm running into an issue where if a report retrieves 0 records, I run into an error. My report has 2 queries-- the main query to retrieve Account information and the other to return Contact information based on the contact id(s) returned by the Account query. The reason needing two separate queries is that the contact and account tables are on different databases.
Now here is my account query: select contact_id, username, password from account. My contact query looks like this: select name, address, .... from contact where contact_id = @contact_id.
In my report parameters, I define contact_id like this: ******************************************* name = contact_id data type = integer prompt = [blanked out] hidden = checked internal = checked multi-value = unchecked allow null value = checked allow blank value = unchecked
available values: from query: dataset = account value field = contact_id
label field = contact_id
default values: from query: dataset = account value field = contact_id **********************************************
Now, when I run my report and knowing there are no records, I get the following error: "The 'contact_id' parameter is missing a value". Any ideas on how to solve this issue? Thanks.
In sql I perform the following SELECT * FROM xlsdci x LEFT OUTER JOIN fffenics f ON f.[derived deal code] = x.[manual dcd id]
which gives me a row count of 2709 rows
In SSIS I have a merge join component (left outer) left input = xlsdci with a sort order of 1 ASC on [manual dcd id] (OLE DB source component) right input = fffenics with a sort order of 1 ASC on [derived deal code] (OLE DB source component)
which when run in the IDE gives me a rowcount of only 2594 rows
Why is this so?
Also if I change the join to INNER in the merge join, the number of rows drops dramatically to only 802. Fair enough, I hear you cry, maybe there are IDs in the 'xlsdci' table that are not in the 'fffenics' table. Ok. But the following SQL reveals that there are only 14 rows(IDs) in 'xlsdci' that are not in 'fffenics'
SELECT * FROM xlsdci WHERE [manual dcd id] NOT IN (SELECT [derived deal code] FROM dbo.fffenics)
I have a stored proc which should be returning a datatable. When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code. I'm not getting any errors during runtime. Any help at all would be a BIG help! private void PopulateControls() { DataTable table = CartAccess.getCart(); } public static DataTable getCart() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "sp_cartGetCart"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@CartID"; param.Value = cartID; param.DbType = DbType.String; param.Size = 36; comm.Parameters.Add(param); DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm)); return table; } public static DataTable ExecuteSelectCommand(DbCommand command) { // The DataTable to be returned DataTable table; // Execute the command making sure the connection gets closed in the end try { // Open the data connection command.Connection.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = command.ExecuteReader(); table = new DataTable(); table.Load(reader); // Close the reader reader.Close(); } catch (Exception ex) { Utilities.SendErrorLogEmail(ex); throw ex; } finally { // Close the connection command.Connection.Close(); } return table; }
I'd like to return the following result set: CompanyModules.CompanyID | Modules.Name | Present 1 | A | True 1 | B | True 1 | C | True 2 | A | True 2 | B | False 2 | C | False
What would be the query for this? Thanks.
Edit: This is the query I have tried:
select CompanyModules.CompanyID, Modules.Name, count(Modules.ID) as Present from
CompanyModules RIGHT outer Join Modules on CompanyModules.ModuleID = Modules.ID
group By CompanyModules.CompanyID, Modules.Name
Order by CompanyID
However, it only returns a partial result set:
CompanyModules.CompanyID | Modules.Name | Present 1 | A | 1 1 | B | 1 1 | C | 1 2 | A | 1
I have a xml file downloaded from webAPI which i want to import into my database.There are several sub childnodes and need to combine data between nodes.
I'm running the following test query on a single table:
SELECT sph.datestamp, sph.stocksymbol, sph.closing, DATENAME(dw, sph.datestamp), CASE DATENAME(dw, sph.datestamp)Â Â Â WHEN 'Monday' then 'Monday'Â Â ELSE (SELECT CAST(sph2.datestamp AS nvarchar) FROM BI_Test.dbo.StockDB AS sph2 WHERE sph2.DateStamp = DATEADD(d, -1, sph.datestamp) AND sph2.StockSymbol = 'NYA')Â END AS TestCase,
[Code] ....
And here's an example of the output I'm getting:
Why the exact same subquery in the THEN of the second CASE statement is returning NULL when the first one completes as expected?
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)
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
I hvae a stored procedure that has this at the end of it: BEGIN EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, '' END SET NOCOUNT OFF
SELECT Something FROM Something Joins….. Where Something = Something now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure. What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
I need to get a limited set of records from sorted record set. For example, I have a table called Contacts which contains about 2000 records and I need to show records from 11 to 20 in the sorted order.
In Oracle I could use this query.
SELECT name FROM (SELECT name, ROWNUM rnum from Contacts order by Name) WHERE rnum between 11 and 20
Is anything equivalent available in SQL Server ? My need is to show records page by page ( as in google/yahoo search ) in my asp page.