Help! How Do I Supress Return Results In SP
Nov 11, 2003
I'm executing this dynamic query in the middle of stored proc to get the @hrs2 value:
select @sql=('select @hrs2 = sum('+quotename(@day2)+') from #pso_view where id = ')
select @sql= @sql+cast(@num as varchar(5))
EXEC sp_executesql @sql,N'@hrs2 int OUTPUT',@hrs2 OutPut
SELECT @hrs2
That works, but then it returns a row result with every exec and that messes up my desired row return from the contaner sp for my web control databind
Any tips on how to get the varible populated via the dynamic sql w/o rows return?
THANKS!!!!!!!!
View 2 Replies
ADVERTISEMENT
Mar 13, 2015
I am trying to get this query to the point where it will return only results that meet specific conditions. Here is the current query I have:
use mdb
SELECT call_req.ref_num, prob_ctg.sym, act_type.sym, act_log.description
FROM call_req
INNER JOIN prob_ctg
ON call_req.category = prob_ctg.persid
INNER join act_log
ON call_req.persid = act_log.call_req_id
INNER JOIN act_type
ON act_log.type = act_type.code
where prob_ctg.sym like 'rd1.%' and prob_ctg.auto_assign=1 and call_req.active_flag=0 ORDER BY call_req.ref_num, act_log.time_stamp DESC
This produces results as follows:
I3835493FY15 RD1.Desktop Transfer Transfer Asignee from " to Jordana, Jose"
I3835493FY15 RD1.Desktop Close Status changed from 'Resolved' to 'Closed'
I3835493FY15 RD1.Desktop Close Status changed from 'Open' to 'Resolved'
I3835493FY15 RD1.Desktop Event Occurred AHD05447 No eligible Request Locations
I3835493FY15 RD1.Desktop Initial Create new request/incident/problem
[Code] .....
This query gives me the total number of records that meet my overall criteria, but what I need to have is a count of the total distinct number of call_req.ref_num and then I need a count of the number of records where act_type.code = TR and the text of act_log.description contains text including "Transfer Group From" and then I need a count of the records where act_type.code = EVT and the text of act_log.description contains text including "AHD054".
View 6 Replies
View Related
Nov 20, 2006
USE AdventureWorksGO-- Retrieve order manifestSELECT SalesOrder.SalesOrderID,SalesOrder.OrderDate,SalesOrder.AccountNumber,SalesOrder.Comment,Item.ProductID,Item.OrderQtyFROM Sales.SalesOrderHeader SalesOrderJOIN Sales.SalesOrderDetail ItemON SalesOrder.SalesOrderID = Item.SalesOrderIDWHERE SalesOrder.SalesOrderID = 436591. Examine the query in the file, noting that it retrieves data from theSales.SalesOrderHeader and Sales.SalesOrderDetail tables in theAdventureWorks database.2. ON the toolbar, click Execute.3. Review the query results.?Modify the query to retrieve <rowelements1. Make the necessary changes to the query to return the data in thefollowing XML format (hint: use FOR XML).Thanks In Advance,~CK
View 3 Replies
View Related
Mar 24, 2004
Hi,
I have this problem with MS SQL Server.
I have this table 'Request' in which there's 2 fields 'RequestName' and 'DateSubmitted'.
The datatype for 'DateSubmitted' is datetimn.
I have to write a query to extract all the 'RequestName' that falls under each month based on the 'DateSubmitted'.
For example, the table 'Request' has 12 rows, each row ('DateSubmitted') with the month of the year. Like row1 = January, row2 = February etc.
For each month, I have to extract all the 'RequestName' for that month based on 'DateSubmitted' field.
My query is like this:
select RequestName, datename(month, DateSubmitted)as month
from Request where datename(month, DateSubmitted) = 'March'.
By right it should return just one row right?
Instead, it returned me all 12 rows, only one row with value 'March' and the other 11 rows with value 'NULL'. Even if the other 11 rows have value of other months, when i run the above query, it will return me null.
Any help is appreciated.
thanks a million.
View 10 Replies
View Related
May 29, 2008
Hi,
I am new to MDX and I have created a query listed below, this returns the correct information from the cube. However when I split the query into a CREATE SET and Query the data returned is wrong. I need to include the set creation in the cube but this returns the wrong information. I thought that information returned by these two queries would be indentical can anyone explain please.
Thanks David
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0) on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
----------------------------------------------------------------------------------------------------------------------------------------
create SET [Finance].[LFL Stores List] AS
Filter (([Store].[Store No].[Store No].Members),
([LFL Month Store].[Month Lf L Store].&[Month LfL Store]) <> 0)
SELECT
({[Time Calculations].&[Current Period],[Time Calculations].[Prior Year]}) on columns,
[LFL Stores List] on rows
from finance
where( [LFL Calendar].[LFL Calendar Hierarchy].[Year].&[2008].&[Qtr 1 2008].&[P3:April 2008] ,
[Measures].[GL Amount])
View 8 Replies
View Related
Oct 8, 2004
Two questions - one slightly off topic:
1) How do I write a stored procedure in MS SQL so that it returns a results set?
2) How do I get this into ASP.NET so that I can put the values into controls?
Jags
View 1 Replies
View Related
Apr 16, 2013
I have a table that lists states that I need to see order info from. I am attempting to create a SQL query that displays the production data for those states. So if the state is in my saleState table I want it to show in my query result even if there were 0 sales to that state. Below is the syntax I am trying, but if the count is 0, it is not returning the sales state it is omitting it.
Code:
Select a.state, COUNT(b.recordID)
From saleState a left outer join tblSales b
On a.state = b.state
And b.orderDate between '01/01/2012' AND '12/31/2012'
Need to point out the error in my query syntax?
View 14 Replies
View Related
Sep 30, 2006
I need to return the results of a stored procedure into a temporary table. Something like this:
Select * into #temp from exec (the stored procedure). It appears that I can not do this.
The following will not work for me cause I am not sure how many columns will be returned. I want this to work even if the calling stored procedure is changed (i.e add or take away columns)
insert into (...) exec (the stored procedure.
Does anyone have any ideas how I could do this.
View 4 Replies
View Related
Aug 1, 2006
I thought I would impliment a new feature of my web page using stored procedures and the SqlDataSource object, for practice or whatever, since I don't normally use that stuff.
This is the stored procedure:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Lance Colton
-- Create date: 7/31/06
-- Description:Find the contest winner
-- =============================================
ALTER PROCEDURE [dbo].[AppcheckContest]
-- Add the parameters for the stored procedure here
@BeginDate datetime = '1/1/2006',
@EndDate datetime = '12/31/2006',
@SectionID int = 10,
@WinnerID int = 0 OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT top 1 @WinnerID = P.UserID
FROM cs_Posts P
WHERE P.PostDate BETWEEN @BeginDate AND @EndDate
AND P.SectionID = @SectionID
AND P.UserID <> 2100 -- I don't want to win my own contest...
AND SettingsID = 1000 -- This number could be different if i had more than one CS installed?
AND IsApproved = 1
ORDER BY NEWID() -- yes this is slow, but it works...
RETURN @WinnerID
END
It's really simple - just needs to return the one randomly chosen integer userID. I've tested it in query designer or whatever it's called in Management Studio and it works fine there at least.
Thinking I was done the hard part, I created a new web form in visual studio, dropped a SqlDataSource on it, and used the 'configure data source' wizard from the smart tag to do all the work for me. I didn't have any trouble using the wizard to select my stored procedure, and i'm using the sa connection string to simplify my debugging. I tried using the FormParameter / FormField way of getting the output and setting the input parameters. I can't seem to get it working though. There's no errors or anything, just the output isn't coming through.
Here's the code from the aspx codebehind file:Partial Class Contest
Inherits System.Web.UI.Page
Protected Sub btnSelectWinner_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSelectWinner.Click
Dim stuff As New System.Web.UI.DataSourceSelectArguments
SqlDataSource1.Select(stuff)
SqlDataSource1.DataBind()
lblWinnerID.Text = SqlDataSource1.SelectParameters("WinnerID").ToString
End Sub
End Class
As you can see, I wasn't sure if you're supposed to call databind() or select() to actually get the stored procedure to execute, so I tried both. I was hoping the last line of code there would set the label to the value contained in the @WinnerID parameter, but instead it sets it to "WinnerID".
Here's the code from the .aspx file. Most of this was generated by the Wizard, but I messed around with it a bit. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="Contest.aspx.vb" Inherits="Contest" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="CPHMain" Runat="Server">
<asp:Button ID="btnSelectWinner" runat="server" Text="Find Winner" />
<asp:Calendar ID="Calendar_From" runat="server"></asp:Calendar>
<asp:Calendar ID="Calendar_To" runat="server"></asp:Calendar>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:appcheck-csConnectionString-sa %>"
SelectCommand="AppcheckContest" SelectCommandType="StoredProcedure" CancelSelectOnNullParameter="False">
<SelectParameters>
<asp:FormParameter FormField="Calendar_From" Name="BeginDate" Type="DateTime" />
<asp:FormParameter FormField="Calendar_To" Name="EndDate" Type="DateTime" />
<asp:Parameter DefaultValue="10" Name="SectionID" Type="Int32" />
<asp:formParameter FormField="lblWinnerID" defaultvalue="666" Direction="InputOutput" Name="WinnerID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
<asp:Label ID="lblWinnerID" runat="server" Text="???"></asp:Label>
</asp:Content>
View 3 Replies
View Related
Aug 9, 2000
I have a search stored proc which fails to return results when called by more than one user.
I have put selects in the various SPs to trace results and if I call from 3 query windows (executnig each as quickly as I can work the mouse) I get the following:
1st query returns the trace values (including correct count of temp table recs) but no result set
2nd query erturns nothing just "The command(s) completed successfully."
3rd query returns full results.
This seems to be consistent.
We are running SQL Server 7 SP1.
Just upgrading to SP2 to see if that helps.
The main SP calls other SPs to build result sets.
These use quite a few temp tables passed between SPs, parse CSV lists, join different other tables, create a SQL string to exec to do the search and get record IDs to return (no cursors).
The result set is built by a called SP using the temp table with IDs to return.
Anyone know of any problems or can suggest anything to try?
View 3 Replies
View Related
May 27, 2015
How can I return results from this SP?
Alter
Procedure sp_Blocking
as
SET NOCOUNT
ON
truncate
table blocked
[Code] ....
View 4 Replies
View Related
May 21, 2006
I have concatenated a long Select Statement and assigned it to a variable. (i.e.)
@a = Select * from foo ---Obviously mine is much more robust
How do I execute @a to actually Select * from foo?
View 3 Replies
View Related
Jun 5, 2006
Can I use sqlDatareader with a stored procedure to return more than one reultset that I ll be accessing through MyDatareader.MoveNext
Any Little example on both the stored proc and the .Net side
Thanks
View 4 Replies
View Related
Oct 7, 2004
I want to send 1 email with all clientname records which the cursor gets for me.
My code however is sending 1 email for 1 record i.e clientname got from db. What's wrong? please help.
I ano table to understand here about the while if right.
thanks.
+++++++++++++++++++++++++++++++++++++++++
CREATE PROCEDURE test1
AS
declare @clientName varchar(1000)
declare myCursor CURSOR STATIC
for
select client_name
from clients
-------------------------
-- now prepare and send out the e-mails
declare @ToEmail varchar(255)
declare @FromEmail varchar(255)
declare @Subject varchar(255)
declare @Body varchar(2000)
declare @UserID numeric(38)
declare @UserName varchar(255)
declare @SMTPServer varchar(100)
set @SMTPServer = 'test.testserver.com'
-- loop for each record
open myCursor
fetch next from myCursor
into @clientName
--loop now:
while (@@fetch_status=0)
begin -- while(@@fetch_status=0)
-- check if valid "To" e-mail address was found
if ((@clientName is null) or (ltrim(@clientName) = ''))
begin
--should not come here anytime ideally
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = 'was emailed to wrong person'
set @Body = 'the client name got is : '+ @clientName + 'client is null or empty'
end --if
else
begin
set @FromEmail = 'me@test.com'
set @ToEmail = 'me@test.com'
set @Subject = '-testing'
set @Body =
'this will send
ClientName:'+ @clientName
end --end else
-- send the e-mail
--exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
--fetch next from myCursor into @clientName
fetch next from myCursor
into @clientName
end --while(@@fetch_status=0)
exec dbo.usp_SendCDOSysMailWithAuth @FromEmail, @ToEmail, @Subject, @Body, 0, @SMTPServer
close myCursor
deallocate myCursor
GO
View 1 Replies
View Related
Nov 1, 2014
I have rather simple CLR function:
[SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
public static SqlString GetUserName()
{
return (SqlString)WindowsIdentity.GetCurrent().Name;
}
When I get result from .NET console app, I get correct answer "JungleSektor". However, when SQL Server executes this code, it gives me "NT ServiceMSSQL $ SQL2014". How to get correct result?
View 1 Replies
View Related
May 15, 2007
Hi
I regularly use the T-SQL date functions to return the 1st of a particualr month.
e.g.
SELECT DATEADD(m,DATEDIFF(m,0,getdate()),0)
returns 2007-05-01 00:00:00.000
i.e the first of the current month at midnight.
However, when I try to use a similar expression as a derived column in SSIS it returns a completely different date.
DATEADD("month",DATEDIFF("month",(DT_DATE)0,GETDATE()),(DT_DATE)0)
returns 30/05/2007 00:00:00
Any ideas why and how I can obtain the first of a particualr month using SSIS derived column?
View 3 Replies
View Related
May 22, 2001
Hi, I wanted to create an exact database in another server, so I generated script of all tables,views, store procedures and ran the script on the new server. I was able to have all objects in the new server.
but when I run the following sql from sql query analyser, I get nothing in return. What do I need in order to get a valid response.
Ali
/*Truncate from all tables*/
select 'Truncate table ' + name from sysobjects where type ='u'
order by name
/*Count all table rows from all tables*/
select 'select count(*) as ' +''+ name + ' from ' + name from sysobjects where type ='u'
order by name
/*View all sp*/
select * from sysobjects where type ='p' and name
not like 'dt%'
order by name
/*View all triggers*/
select * from sysobjects where type ='tr'
order by name
/*View all Views*/
use master
select * from sysobjects where type ='v'
order by name
View 1 Replies
View Related
Nov 10, 2007
Hi, Could you tell me if this is possible? How do I return results using an entire column as part of the search parameter? I need to do this in the sql rather than selecting the contents and iterating through it as it would take too long.
eg.
CREATE TABLE [dbo].[tPopupKeywords](
[id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[title] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[description] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
INSERT INTO dbo.tPopupKeywords(title, description)
SELECT 'check', 'desc' UNION ALL
SELECT 'for', 'desc' UNION ALL
SELECT 'keywords', 'desc'
select dbo.tpopupkeywords.title
where 'This is an example of a passed in string to check if any keywords are returned.'
LIKE '% ' + dbo.tpopupkeywords.title + ' %' --Does this bit need to do a select??
expected results.....:
check
keywords
View 1 Replies
View Related
Dec 4, 2014
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS]
GO
/****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT
ee.EmployeeID,
EmployeeName = LastName + ', ' + FirstName
FROM EmployeeInvData ee
--UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
View 4 Replies
View Related
Aug 15, 2007
I am unable to get FTS working where the column to be searched is type varchar(MAX) or Text. I can get this to work if my column to be indexed is some statically assigned array size such as varchar(1000).
For instance this works, and will return all applicable results.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](1000) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);
And this does not. It returns zero results what so ever.
CREATE TABLE [dbo].[TestHtml](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageText] [varchar](MAX) NOT NULL,
CONSTRAINT [PK_TestHtml] PRIMARY KEY CLUSTERED
SELECT * FROM TestHTML WHERE Contains(PageText, @searchterm);
Could someone please tell me what I need to do to enable FTS on varchar(MAX) or Text columns?
View 1 Replies
View Related
Mar 7, 2007
Microsoft could not have made SQL Server 2005 Reporting Services any harder given the wacky way you have to add web references in Visual Studio 2005 and then when you finally get the report services working as well as the C# program compiling and seeing the
Here are the steps for you poor souls like me:
When you create you C# program Maybe its the same thing for vb rightclick on the project
and click add Web Reference
To the right of the go button you must put in a path to:
http://<server>/reportserver/ReportExecution2005.asmx?wsdl
If you used the default ReportServer settings this should work You will need to make sure you have ReportServices working first but by entering this exact URL and press the go button allows you to acess the ReportExecutionServcie object ofcourse you need to properly rename it in the text box in the lower right labled: Web Reference Name: I renamed it to ReportExecution2005
Note us must add a using <myclassname>.ReportExecution2005; which allows access to the the components and allos compiling.
ReportExecutionService
object correctly and want to use them in C# program Microsoft makes you suffer further with problems getting the path in rs.LoadReport(ReportPath,HistoryID) function
I believe I have the correct path string from trial and error when I go to my report services site
It shows me which as a guess I thought might be derived from the webpage:
http://myserver.mydomain.com/Reports/Pages/Report.aspx?ItemPath=%2fReport+Project2%2fKSConcordanceErrorReport&SelectedTabId=PropertiesTab&SelectedSubTabId=GenericPropertiesTab
displaying:
SQL Server Reporting Services
Home > Report Project2 >
KSConcordanceErrorReport
/home/Report Project2/KSConcordanceErrorReport but that failed to work. Next I tried:
The RichTextBox stored:
The item '/home/Report Project2/KSConcordanceErrorReport' cannot be found. ---> The item '/home/Report Project2/KSConcordanceErrorReport' cannot be found.
/Report Project2/KSConcordanceErrorReport
??t was all that was returned when I tried this path so I believe but am (Not Sure) this is the reportpath to be used in rs.LoadReport function.
In the browser: I believe the catalog based reportpath should be
/Report Project2/KSConcordanceErrorReport
I set up a form with 3 fields:
TextBox: ReportPath so I could play with the path I provide
RichTextBox To store the output from rs.render.
RunButton1 to call up the render code see below:
Please note when I start the program in debug mode I get an error of:
Note the following message proceeds the execution:
The Project cannot be deployed because no target server
is specified. Provide a value for the TargetServerURL
property in the property pages for this project
Any help in getting output on this render function would be appreciated
Perhaps some of my work will help others struggling with this poorly documented service. Even the three poor books I had to work with did not help much. Does Microsoft really expect people to use this?
Here the relevant code. I will assume knowledge of Forms development I provide two pieces: 1 the calling function from the button presss event: and the method called:
richTextBox1.Text=EmbededReportx.Program.GetReportXML2005(
string.Format("http://myserver.mydomain.com/ReportServer/R
eportExecution2005.asmx"),ReportPathTextBox.Text);
public static string GetReportXML2005(string
ReportingServicesURL,string ReportPath)
{
ReportExecutionService rs = new
ReportExecutionService();
//windows authentication
rs.Credentials =
System.Net.CredentialCache.DefaultCredentials;
rs.Url = ReportingServicesURL;
byte[] result = null;
ParameterValue[] parameters = new
ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name="user2report";
parameters[0].Value = "%";
string encoding, mimetype, extension;
Warning[] warnings = null;
string[] streamIDs = null;
try {
rs.LoadReport(ReportPath, null);
rs.SetExecutionParameters(parameters,
"en-us");
result = rs.Render("XML", null, out extension, out encoding,
out mimetype, out warnings, out streamIDs);
}
catch (SoapException e) {
return e.Message;
}
return System.Text.Encoding.ASCII.GetString(result);
}
Any help anyone could give me regarding this would be most appreciated:
View 1 Replies
View Related
May 7, 2013
I have a FullTextSQLQuery which I am trying to search a phrase(The Multi-part identifier) on full text indexed table. I am getting expected results on running the below sql query on QA machine and PreProduction servers, but not getting the same results on our development and production servres as even though same code running.
SELECT DISTINCT TOP 50 c.case_id,c.status_id,cal.cas_details
FROM g_case_action_log cal (READUNCOMMITTED)
INNER JOIN g_case c (READUNCOMMITTED) ON (cal.case_id = c.case_id)
INNER JOIN CONTAINSTABLE(es.g_case_action_log, cas_details,
' "The multi-part identifier" OR "<br>The multi-part identifier" OR
"The multi-part identifier<br>" ') as key_tbl
ON cal.log_id = key_tbl.[key]
ORDER By c.case_id DESC
We are using SqlServer 2008 R2 version on all servers.
View 1 Replies
View Related
Jul 20, 2005
I have the following code to SUM based on one field but want to be ableto not show rows which result is 0.========================SELECT Headers.employee_id AS Employee#,SUM(CASE Headers.scan_type WHEN 'I' THEN 1 ELSE 0 END) AS SIP,SUM(CASE Headers.scan_type WHEN 'F' THEN 1 ELSE 0 END) AS PUP,SUM(CASE Headers.scan_type WHEN 'Q' THEN 1 ELSE 0 END) AS CMT,SUM(CASE Headers.scan_type WHEN 'M' THEN 1 ELSE 0 END) AS CONS,SUM(CASE Headers.scan_type WHEN '' THEN 1 ELSE 0 END) AS HIP,SUM(CASE Headers.scan_type WHEN 'U' THEN 1 ELSE 0 END) AS HOP,SUM(CASE Headers.scan_type WHEN '^' THEN 1 ELSE 0 END) AS INSTA,SUM(CASE Headers.scan_type WHEN 'L' THEN 1 ELSE 0 END) AS ONRD,SUM(CASE Headers.scan_type WHEN 'B' THEN 1 ELSE 0 END) AS POD,SUM(CASE Headers.scan_type WHEN 'G' THEN 1 ELSE 0 END) AS PUX,SUM(CASE Headers.scan_type WHEN '#' THEN 1 ELSE 0 END) AS ROP,SUM(CASE Headers.scan_type WHEN 'J' THEN 1 ELSE 0 END) AS SOP,SUM(CASE Headers.scan_type WHEN 'N' THEN 1 ELSE 0 END) AS STAT,SUM(CASE Headers.scan_type WHEN 'R' THEN 1 ELSE 0 END) AS UNCON,SUM(CASE Headers.scan_type WHEN '<' THEN 1 ELSE 0 END) ASUSPS_PSP,SUM(CASE Headers.scan_type WHEN '"' THEN 1 ELSE 0 END) AS RIP,COUNT (Scans.header_index) AS EmpTotFROM Headers INNER JOINScans ON Headers.header_index = Scans.header_indexWHERE (Scans.datetime BETWEEN '06/18/2003 00:00:00' AND'06/19/2003 23:59:00')GROUP BY Headers.employee_idORDER BY Headers.employee_id===============================Any ideas?Thanks,Manuel--Posted via http://dbforums.com
View 9 Replies
View Related
Oct 4, 2007
Hi !
I am working on Converting existing Crystal reports to Reporting Services. Now I have a line in Crystal reports which has a formula for supression. I dont know whats the equivalent of supress in SSRS. I have tried Visibility as hidden but then it shows empty line but not supressed line.
View 2 Replies
View Related
Apr 19, 2014
I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
Query
DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);
SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');
SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result
[Code] ....
View 2 Replies
View Related
Jan 31, 2007
How do I get a Report Table to supress blank rows from displaying ?
In Crystal Reports this option is available under Format Detail Section, supress blank section chekckbox.
View 9 Replies
View Related
Feb 12, 2007
Cannot find this anywhere while creating a report. Is this an option?
thanks.
View 2 Replies
View Related
Oct 5, 2001
Hi
Can we supress the System error messages ?
if yes how ?
for ex. In a table I am adding a row which is voilating primary key constarint ..I do not want system message for that ..
Server: Msg 2627, Level 14, State 1, Line 0
Violation of PRIMARY KEY constraint 'PK_b'. Cannot insert duplicate key in object 'b'.
The statement has been terminated.
I can write a user defined message for this purpose but I am not able to supress system message .
The procedure I am using is
CREATE PROCEDURE add_b
@num int,@empno int, @name varchar(6)
AS
INSERT INTO B (num, empno, ds ) values (@num,@empno,@name)
-- Test the error value.
IF @@ERROR <> 0
BEGIN
-- Return 99 to the calling program to indicate failure.
RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
PRINT "An error occurred loading the new author information"
RETURN(99)
END
ELSE
BEGIN
-- Return 0 to the calling program to indicate success.
PRINT "The new author information has been loaded"
RETURN(0)
END
GO
Sujit
View 2 Replies
View Related
Apr 23, 1999
I have a stored procedure where in a cursor I create a dynamic select
statement. If I have a non-zero result set (check @@rowcount) I output a message. The problem is I do not know how to supress the output of the
execute of the dynamically created select statement. All I want going to
output is the message. I know ISQL has this function, and so does EM. How do I do it totally within a stored procedure.
View 2 Replies
View Related
Jul 20, 2005
HiI have a dynamically constructed sql query that I want to execute, e.g.exec('select * from ' + @tablename)(1) Can I suppress the output somehow if this returns no values?(2) Can I use the result of this query in another query somehow? e.g.select(3) Can I control the size of the columns in the output somehowThanksF
View 3 Replies
View Related
Dec 26, 2006
I have several columns with expressions that return Nothing under certain conditions:
Example:=(IIF( Fields!solved.Value=False,IIF( Fields!TAT3.Value >120, IIF(Fields!TAT3.Value <144,Fields!Ticket.Value,Nothing),Nothing),Nothing))
As a result, I get tons of white/empty space, because 200 values were evaluated as Nothing and only 5 values/records were actual numbers.
How can I supress this Nothing value ?
View 4 Replies
View Related
Aug 24, 2007
Hello -
Does anybody know of a way to force SSRS to render a report with no page breaks at all? And better yet, to render as such but export using breaks (I know, thats asking a lot but the first part seems to be a reasonable request)
I have 1 report that renders a lot of data with no page breaks and cannot find a SINGLE property difference from the reports that do break...
Thanks in advace,
Gary
View 3 Replies
View Related
Feb 21, 2008
Is there any way to supress the popup window on the export option so that the first thing the user see's is the open/save dialog?
Alternatively
Is there a way to make the window close when the open/save dialog appears?
Thanks
Mike
View 7 Replies
View Related
