I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows.
I thought the connection is not the problem since I got my columns right. Below is my code snippet.
@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True") Dim cmd As New SqlCommand
Dim returnValue As Object cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)" cmd.CommandType = Data.CommandType.Text cmd.Connection = sqlConnection3 cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar) sqlConnection3.Open() cmd.Parameters("@Names").Value = test If test = "" Then Response.Write("An error occured") Exit Sub Else returnValue = cmd.ExecuteScalar() sqlConnection3.Close() Label6.Text = "Number " & returnValue
hi, im new to this site so i don't know if i'm posting in the correct forum. anyway, this is my code:---Dim dbMac As DBLibrary = Nothing dbMac = New DBLibrary(General.GetMACConnectionString)dbMac.OpenConnection("SPR_STAFFMAIN_GETEMPLOYEERECORDS")dbMac.CreateParameter("@USERENTITYID", GetUserEntityID(), Data.SqlDbType.Int)drpEmpNumbers.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataSource = dbMac.GetDataView("StaffMaintenance")gridStaffMaintenance.DataBind()---DBLibrary is a class that opens a connection to the SQL server. i'm getting an empty grid even though the stored procedure returns a row when i test it in the analyzer. is there to debug or test this code? thanks!
To create a strike file for a text file output, I piped the output of a query throught a mulitcast. One output is the actual flat file, the other output is the strike file. The next task for the strike output is an aggregate count and sum functions. If the original query returns no records, the count shows zero, but the sum shows NULL. Further down the process I have to pad out the values with zero's, but that NULL is causing problems. How do I convert the Null to either an empty string, or a value of zero? Thanks
Hi, whenever the underlying query being called by EXEC in the followinghas an empty result set I get the following error -- Invalid Data for'Numeric' when EXEC returns empty row. However if I call the querywithout using REPLACE (which I'm forced to do, because openquery doesnot allow variables), I get just an empty result set. Whenever theunderlying query returns a non-empty result set, the code works withouterror (regardless of wether there are nulls in the numeric column).set @switch ='5707550'set @start_date = '01-JAN-2006'set @end_date = '27-JAN-2006'set @month = 1set @year = 2006set @sql_str='SELECT * FROM(select MSC_KEY,to_char(trunc(TSTAMP), ''yyyy-Mon-dd'') as "Timestamp",ROUND( NVL(SUM(SUNRGMMSCBHCP1.XASUTIL),0) / DECODE (NVL(SUM(SUNRGMMSCBHCP1.XASNXFR),0),0,NULL,NVL(SUM( SUNRGMMSCBHCP1.XASNXFR),0)), 5)as "PER_CPU_UTIL"FROM NOR_GSM_COMPOSITE_MSC1_BHCPP SUNRGMMSCBHCP1,mscs_view vWHERE SUNRGMMSCBHCP1.gsm_msc_key = v.msc_key and v.MSC_KEY in (' +@switch + ')and SUNRGMMSCBHCP1.TSTAMP between to_date(''' + @start_date + '00:00:00'', ''DD-MON-YYYY HH24:MI:SS'') andto_date(''' + @end_date + ' 23:59:00'', ''DD-MON-YYYYHH24:MI:SS'')group by MSC_KEY, trunc(tstamp))WHERE rownum < 10000'SET @sql_str = N'select * from OPENQUERY(VISION, ''' +REPLACE(@sql_str, '''', '''''') + ''')'EXEC (@sql_str);Is there anyway to prevent this error?Thanks,Crazy
In sql server 2000 - our QA pointed out that his testing for empty strings returned 200 + rows but that when he clicked in the field there were obviously a space there. This issue came up because of the script I created to replace and earlier one that queried on empty strings instead of datalength and the earlier script always reported that it had updated x number of rows regardless of how many times it was run on the same database.
QA query based on the earlier script: Select * from StringTable WHERE (LongString = '' OR LongString IS NULL)
My script: The fields are nvarchars in the newer database but older version of the database had varchars. I had created a script to replace empty strings as follows:
-- if LongString column is varchar - run varchar update else nvarchar update If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'LongString' and o.name = 'StringTable' and c.xtype = 167) begin
-- update varchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 1) OR LongString IS NULL)
END Else Begin
-- update nvarchar LongString UPDATE StringTable SET LongString = char(32) -- Select * from StringTable WHERE ((DATALENGTH(LongString ) < 2) OR LongString IS NULL)
END
If exists (Select * from sysobjects o inner join syscolumns c on c.id = o.id where c.name = 'ShortString' and o.name = 'StringTable' and c.xtype = 167) begin
UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 1) OR ShortString IS NULL)
END Else Begin
-- update nvarchar ShortString UPDATE StringTable SET ShortString= char(32) -- Select * from StringTable WHERE ((DATALENGTH(ShortString) < 2) OR ShortString IS NULL)
END
My method for checking for datalength appears to work correctly why doesn't the QA script? I thought it might have to do with the nvarchar used in the table but I changed the column to a varchar and still has the same issue.
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.
After changing the Service account for a SQL Server 2005 SP2 machine the DMV sys.dm_os_performance_counters is returning zero rows. These values were availiable prior to change
The change in Service account was done via the Configuration manager and the Service account is also part of the Local Administrator group
Since this change the Performance Counters in Perfmon for SQLSEVERatabases and others are also not availiable
Hi everyone: I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting? SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2 This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all. Thanks for any help.
I am trying to build a association table (t2) to store a list of users have viewed an item in my records table (t1). My goal is to send the UserID parameter to the query and return to the user a read / not read marker from the query so I can handle the read ones differently in my .net code. The problem is that I cannot work out how to return anything but the read data to the client. So far my stored proc looks like this
DECLARE @UserID AS Int -- FOR TESTING SET @UserID = 219 -- FOR TESTING
SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID
FROM t1 LEFT OUTER JOIN t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @UserID
GROUP BY t1.MemoID, t1.strTitle,t2.UserID
It works fine but only returns those records from t1 that are read. I need to return the records with null values also! I may have built the assoc table wrong and would really appreciate some pointers on what I am doing wrong. (assoc table has rID, MemoID and UserID columns)
I have DB monitoring jobs which use Sysperfinfo to monitor some of the counters. On One SQL 2K Server since few days the select on sysperfinfo returns 0.
Do you think I need to start any service from the OS side to enable this?
select sq.*, p.numero, p.nombre from paf p right outer join dbo.GetListOfSquaresForShippingLot(@lot) sq on sq.number = p.numero and sq.version = p.numero
The @lot parameter is declared at the top ( declare @lot int; set @lot = 1; ). GetListOfSquaresForShippingLot is a CLR TVF coded in C#. The TVF queries a XML field in the database and returns nodes as rows, and this is completed with information from a table.
If I run a query with the TVF only, it returns data; but if I try to join the TVF with a table, it returns empty, even when I'm expecting matches. I thought the problem was the data from the TVF was been streamed and that's why it could not be joined with the data from the table.
I tried to solve that problem by creating a T-SQL multiline TVF that is supposed to generate a temporary table. This didn't fix the problem.
What can I do? Does anybody know if I can force the TVF to render its data somewhere so the JOIN works? I was thinking a rowset function could help, but I just can't figure out how.
PLEASE HELP!!!!
Let me know if you want the code for the CLR TVF. This is the code for the T-SQL TVF:
CREATE FUNCTION [dbo].[GetTabListOfSquaresForShippingLot] ( @ShippingLot int ) RETURNS @result TABLE ( Number int, Version int, Position smallint, SubModel smallint, Quantity smallint, SquareId nvarchar(5), ParentSquareId nvarchar(5), IsSash smallint, IsGlazingBead smallint, Width float, Height float, GlassNumber smallint, GlassWidth float, GlassHeight float ) AS BEGIN INSERT INTO @result SELECT * FROM dbo.GetListOfSquaresForShippingLot(@ShippingLot)
I maintain a simple employment (job) tracking web application.
Jobs can be set to 5 different statuses: Open, Closed, Filled, Pending, or Cancelled.
There is a table in the database called statusLog, which records everytime a job is set to opened, or set to closed, etc. It records the job number, the date it was changed, and what the job was changed to.
Here is a short example of what a few entries might look like:
What I need to do is write an SQL query that will return to me all entries in this table, between two certain dates, that ONLY have entries during those dates.
Basically I need to know how many "new" jobs were set to open during a month. I can easily just do a count of how many jobs were set to open, but this will not give a count of "new" jobs.
Example: during june a job could be set to open. Then in july it could be set to pending. Then in august, it could be re-opened, and set to open.
If I ran this query for the month of August, it would return that job as being opened in august. But it wasn't a new job, meaning it had already been in the system in previous months.
Is there some way I can select all "Open" jobs, between a certain date, that do not exist anywhere in the table previous to the date it was entered? This would give me a result set containing only new jobs.
The only way I've thought of yet is to get a result set of all jobs simply set to open during a month, then one by one for each record, go back and run another SQL query to see if it exists in the table anywhere other than in that month.
This seems horribly inefficient to me however, as I do not want to be doing 34,000 independent SQL calls for every single "open" job it finds during a certain month.
SELECT R.name, R.age,R.DOB, ISNULL(D.Doc1,'NA') AS doc1, ISNULL(C.Doc2,'NA') AS doc2 FROM REQ R inner join RES S ON R.Request_Id=S.Request_Id inner join RES1 D ON D.Response_Id=S.Response_Id inner join REQ1 C ON C.Request_Id=R.Request_Id
select * from RES1 where Response_Id = 111 -- return 3 select * from REQ1 where Request_Id = 222 --- returns 2
So at last inner join retuns 3*2 = 6 records , which is wrong here and i want to show 3 records in doc1 row and 2 records in doc 2 rows ...
Without systemAdmin role, xp_AvailableMedia can still run (after granting execute permission) but just return 0 rows.
It seems we have to give the user SystemAdmin role, in order to get the list of devices in SQL 2005, while in previous versions of SQL Server, the user granted the execute permission for xp_AvailableMedia will be able to get the list of devices.
Is this be design? Is there any way the a NON-SA user can retrieve device list? If yes, what extra permissions do we have grant?
When i try to insert new rows using SSIS into an emty table in my database, SSIS freezes. The same happens when i use a lookup on an empty table.
The only solution i have found is to manualy insert a row and remove it afterwards. When i have done this SSIS works without a glitch.
I start off by taking a script of a existing database and applying this script on a new database. So that i create an empty duplicate of my current database.
Any ideas why SSIS freezes? Do i have to initialize something first in my database? It is really annoying that i have to insert a row and delete a row into every table i want to use.
How do I go about iterating through every table in my database and empty each table so they contain no data? I don't want to drop the tables and re-create them either.
I'm pretty new to .NET and am looking for advice on how to speed up a simple stored procedure that returns 35,000 plus rows. It's a super simple query that returns a client list. It's just that there is soooooo many rows, it's super slow when the page loads.
I am struggling with the below join block in my stored procedure. I can't seem to get the duplicate row problem to go away. It seems that SQL is treating each new instance of an email address as reason to create a new row despite the UNIONs. I understand that if I am using UNION, using DISTINCT is redundant and will not solve the duplicate row problem.
Primary Keys: none of the email address columns are primary keys. Each table has an incrementing ID column that serves as the primary key.
I am guessing I am encountering this problem because of how I have structured my Join statements? Is it possible to offer advice without a deeper understanding of my data model or do you need more information?
Thanks for any tips.
Code:
select emailAddress from Users union select user_name from PersonalPhotos union select email_address from EditProfile union select email_address from SavedSearches union select distinct email_address from UserPrecedence union select email_address from LastLogin) drv Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress) Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID Left Join PersonalPhotos tab2 on (drv.emailAddress = tab2.user_name) Left Join LastLogin tab4 on (drv.emailAddress = tab4.email_address) Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address) Left Join SavedSearches tab6 on (drv.emailAddress = tab6.email_address
Summary * The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats. * As far as I know a fetch-next-statement always returns a single row? * Using a static cursor works as aspected. * Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function create partition function fnTestPartition01( smallint ) as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
I have a query that based 2 tables. I wrote a query with a left join on the base table but the result set returns multiple rows for each occurrence in the second table because of the left join. I want but to return all records from on table A and only matching records from table B which id but I would wan tit to keep return them vertically as the because it make it difficult to read when put in a spreadsheet. It want it to return the values horizontally so the rows are not increasing for each occurrence on table b.
I am using a DTS package where one of the inputs is an Excel Sheet. Actually this sheet is updated manually whenever required i.e once a week or sometimes once a month, but the DTS package runs everyday.
Whenever new rows are added or deleted manually in the excel sheet, empty rows are showed in the sheet after the last row of data. This hinders the DTS package, because the destination table to which the data in the Excel sheet is sent has Primary keys in it.
Can anyone suggest me how to avoid getting the empty spaces in the excel sheet.
Hi, is there a way I can check if a table is empty and contains norows?I have a Table1 which being dynamic can sometimes end up with nowcolumns what so ever. I'm using Table1 in one of my views along with 2other tables and I would like put a condition at the...something likeAND Table1 IS NOTEMPTYIs there a way to do this in MS SQL?Many thanksYas
SELECT NON EMPTY { [Measures].[SUM_COUNT] } ON COLUMNS, TopCount ( Filter ( {[DIM].[NAME].[NAME]}, [Measures].[SUM_COUNT] <> 0 ) , 10, [Measures].[SUM_COUNT] ) ON ROWS FROM [USAGE]
where <criteria>
I want to show the topmost 10 records. For some criteria I get the results in the chart.
But for some criteria or say for wrong criteria, there are no records. In such a case the X-axis contains all values for {[DIM].[NAME].[NAME] and value for the Y-axis is all 0. Its kind of blank report it will restrict to 10 records.
In such a scenario I want to show a message to the user saying "No records found", which I have set in the No Rows property of the chart.
If I remove the TopCount clause then I get the above message, which is obvious.
So how do I acheive the same message but at the same time limiting the records to 10?
How can I acheive this in my scenario? Can something be done at the query end?
I have had at least 2 occurences of a DFT OLEDB source returning the correct number of rows but all rows are empty (they contain zeros or ''). This has happened in two different places in different SSIS packages within an ETL task. In one case the source was on a different server running SQLServer and in the other it was a different database on the same server as the SSIS package. This occured running on different servers, one with SQL 2005 SP2 and the other without. Both are 64 bit AMD systems running 64 bit SQL 2005. As there is a derived column transformation that has performed the derivation on the blank columns I assume the problem is with the OLEDB source but it could be with the data stream from the OLEDB source to to whatever follows. Has anyone else had this problem and does anyone know of a fix?
I have a simple query that joins a largeish fact table (3 million rows) to a view that returns 120 rows. The SKEY in the view is returned via a scalar function. The view returns instantly if queried on it's own however when joined to the fact table in the simple query below results in a query execution plan that runs forever. Interestingly if I change the INNER JOIN to a LEFT OUTER JOIN the query returns the matched results almost instantly.
Select Dimension.Age_Band.[10_Year_Age_Band], Count(*) From Fact.APC_Episodes Inner Join Dimension.Age_Band ON Fact.APC_Episodes.AGE_BAND_SKEY = Age_Band.AGE_BAND_SKEY Group By Dimension.Age_Band.[10_Year_Age_Band]
I know joining to a view using a column generated by a scalar function is not a good recipe for performance. I also know that I could fix this by populating a physical table with the view first as I have already tested this though I hoping not to have to go down that route.
Why a LEFT OUTER JOIN works and not an INNER JOIN or anyway I can get the query optimizer to generate an execution plan that works?
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.
However sometimes the filter I have on a column group returns no records and the whole column group disappears. Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.
In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.
comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID] , MAX([TimeStart]) [LastRunDate] FROM [dbo].[Catalog] [c] LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID] WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source! group by [c].[Name],[c].[ItemID],[xl].[ReportID] order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT * FROM [dbo].[Catalog] WHERE [Type] NOT IN (1,5) -- Not a folder or a data source! AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1' select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
