Transact SQL :: Select From View In SSMS Doesn't Return All Rows
Jun 8, 2015
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList]
as
select m.id as Max4SaleId,
mt.[Description] as [TypeDescription],
CAST(m.[type] as tinyint) as [Type],
m.start_time as [StartTime],
m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of.
It's a very strange thing! I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.
I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do: select * from invoices where invoiceID = 302247 (and i'll get one result) select * from customer_payments where invoice = 302247 (and i'll get none results)
however, if i do the following: select * from invoices where invoice_id not in (select invoice_id from customer_payments) I get nothing!!!???
It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?
Help, is something wrong with my SL Server? I am unable to return any rows from all tables in all databases (user and system)on My SQL 7.0 SP2 machine. Whne i right click on the table in E.M and select design or open table i get no results. Does anyone know why this is happening? It did not always happen either. Thanks
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
Can someone look at this and tell me where I went wrong? I'm trying to return all duplicate rows that have the same lastName and Address. It returns rows but they don't look like dups.SELECT TOP (100) PERCENT dbo.tblClient.LastName, dbo.tblClientAddresses.Address FROM dbo.tblClient INNER JOIN dbo.tblClientAddresses ON dbo.tblClient.Client_ID = dbo.tblClientAddresses.Client_ID GROUP BY dbo.tblClient.LastName, dbo.tblClientAddresses.Address HAVING (COUNT(dbo.tblClientAddresses.Address) > 1) ORDER BY dbo.tblClientAddresses.Address
Can anyone just point me in the right direction. All I want to do is add some T-SQL to an existing stored procedure to return the number of rows selected into a return value.Does anyone know how to do this?
Using TSQL, I have a table that holds filenames of Pictures for products. Different products can be using the same picture. I need to select the filenames for a single product only if it does not exists for a different product.I have tried Where Exists (select FileName From Tbl where
Prod_Id = @var) AND NOT EXISTS(select FileName From Tbl where Prod_Id != @var) In the Select Statement.
I am trying to insert a carriage return in the select statement after the web link where I had highlighted code in bold. When I insert a record into the table, I receive the email with the message body is in single line.I need the result to look like this in the message body:
ALTER TRIGGER [dbo].[SendNotification] ON [dbo].[TicketsHashtags] FOR INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301 302 303 304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code: Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0" For i = 1 To classIDs.Count - 1 assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
I have a stored proc which evaluates a table of 'tasks' to return the task with the highest priority for a given user - at the same time, once that task has been found - I want to allocate that user to the task.
I have created a stored proc that works...however I'm sure this requirement /pattern is common & I would like some best practice for this pattern of update and select within a transaction.
Here is some sample data:
use tempdb; go if OBJECT_ID('Tasks', 'U') is not null drop table tasks; go create table tasks ( TaskId int identity primary key,
[Code] ....
And here's what my current stored proc looks like;
if OBJECT_ID('pGetNextTask', 'P') is not null drop proc pGetNextTask; go create proc pGetNextTask ( @UserID char(10), @TaskID int output )
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as begin Declare @ret int; Declare @EnterVolumeTime int; Declare @ExitVolumeTime int; Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).
Below. I have also pasted the current result of this query and the desired result.
Query can be updated to get the desired result as given below?
Query: Select c.OTH_PAYER_ID, c.PAID_DATE, f.GROUP_CODE, f.REASON_CODE, f.ADJUSTMENT_AMOUNT From MMIT_CLAIM_ITEM b, mmit_tpl c , mmit_attachment_link d, MMIT_TPL_GROUP_RSN_ADJ f where b.CLAIM_ICN_NU = d.CLAIM_ICN and b.CLAIM_ITEM_LINE_NU = d.CLAIM_LINE_NUM and c.TPL_TS = d.TPL_TS and f.TPL_TS = c.TPL_TS and b.CLAIM_ICN_NU = '123456788444'
Current Result which I am getting with this query
OTH_PAYER_ID PAID_DATE GROUP_CODE REASON_CODE ADJUSTMENT_AMOUNT 5501 07/13/2015 CO 11 23.87 5501 07/13/2015 PR 12 3.76 5501 07/13/2015 OT 32 33.45 2032 07/14/2015 CO 12 23.87 2032 07/14/2015 OT 14 43.01
Desired/Expected Result for which I need updated query
I have several views in a database that I need to edit in SSMS 2008.
If I right click on a view, I can select "Design" and do the changes and save. Everything saves ok.
However, I cannot enter comments that way. I have tried View Script As -> ALTER To -> New Query Editor Window but this method creates a new view e.g. SQLQuery1 i.e. I can't save the origial view name.
Is there a way to alter views in code and save them back into the database under their original view name?
In SSMS, when editing a step in a SQL Agent job, on the Advanced screen for a step there is a button called View by the Output File. How do you get that to be enabled? It's always disabled for me and I wish I could view the file right from the job. This is something I miss from 2000. However, the button is there so I would think that I am just doing something wrong.
I want to provide access to one of my users to a database on the production server. I do not want this user to be able to view anything other than the tables in that database, or the other databases on my production server.
I gave him access to one DB - TestDB- and made him data_reader on that DB. I had set that as his default database. However, when he logs in using SSMS he can see from Object Explorer a listing of all the databases on that server, although he can't access any of those. This is an external user and I don't want him to see any of that stuff, including other objects (SPs, Views, etc.,) even within TestDB.
To summarize, I want to grant access to a windows user to see/select from ONLY tables in TestDB of my production server, and I do not want him to be able to see any objects other than tables of this DB from SSMS.
I need to install SSIS/SSRS in SSMS. I have connected the SharePoint list view to excel for reporting purpose but due to data size the performances is very slow in excel and now I would like to connect the SharePoint List to SSIS/SSRS
I have windows 8, 64 bit system
I have installed SQL Server Management studio 2012 but I am unable to enable the Integration and Reporting Services
May be I have missed to choose the option during installation.
Hi all, When I execute a stored procedure that returns a single value using a TableAdapter, I always get error message "Object reference not set to an instance of an object" I followed all the steps in microsoft help center:http://msdn2.microsoft.com/en-us/library/37hwc7kt.aspx but the problem still unsolved. here are my simple stored procedure:create PROCEDURE dbo.StoredProcedure1
AS RETURN 4 and here are the simple code in my asp.net webpage that should display the number "4": protected void Button1_Click(object sender, EventArgs e) { DataSet1TableAdapters.QueriesTableAdapter TA = new DataSet1TableAdapters.QueriesTableAdapter(); int i = (int) TA.StoredProcedure1(); Response.Write(i.ToString());
}
so, please could any one help me solve this problem?
Hi I have written a query for viewing the results of an on-line survey. I have three tables involved in this query: answers, answerpossibilities and users. So I use a few joins and made this query: ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --')) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc The above query works fine. The data returned by this query is shown in a gridview. When an answerpossibilty was never chosen it shows 0 as times chosen. So that's fine. But the problem is, only answers of users who completed the survey should be shown. In the users table there's a field user_completed. So the query should check whether this field is 1 (true). ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --') and users.user_completed = 1) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc Using this query only answers of users who completed the survey are shown but answer possibilities that were never chosen are no longer shown with 0 as times chosen. The gridview simply doesn't show them anymore. Thanks for helping me!
Something went wrong by posting this message I guess, all blank lines were gone.. maybe because I used Safari on my iMac
So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.
Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?
I found this Microsoft article for creating crosstab-like queries in SSMS.Is it possible, however, to create this same query if I do not know what the values for the columns will be? Using their example for my problem, I will not know what the values in the "IN" criteria will be because my query would be for a "rolling" 12 months (thus causing that IN criteria to change every month).I've tried declaring variables to pull in the values, but since this will eventually go into a view, I don't think that I can use declared variables.
I have a written a dll in 2.0 that calls a webservice. This webservice is used to authenticate users in Active Directory. I created a assembly to that calls this dll because of the diverse languages versions that will use it (from asp,vb6 on up) and all can get values from a stored procedure that calls that assembly. I works great. Until now, I have to add another function to my dll that calls the webservice and returns the Users Full name from Active directory for electronic signitures. Okay I added to the dll then tried to reconstruct my Assembly and stored procedures and recieved the following error. "CREATE PROCEDURE failed because a CLR Procedure may only be defined on CLR methods that return either SqlInt32, System.Int32, void" I want to keep all these Active directory call all in one place so I can be consistant in all the different applications. I was reading about UDF but that could get messy as I have a config file for the dll that allows the user to dynamically change the url for the webservice. Any suggestions/help will be greatly appreciated
I am using sql server 2005. I stuck out in a strange problem. I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
