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'
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
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)
I have a SELECT Statement that I am using that is pulling from two tables. There won't always be results in the second table so I made a LEFT OUTER JOIN. The problem I am having is that I need to have three conditions in there:WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE()))The two conditions from the request table are causing the entire query to return NULL as the value. I need help trying get a value whether or not there are any results in the request table.Here is the full select statement:SELECT (SELECT SUM(ISNULL(request.request_duration, '0')) AS Expr1 FROM employee LEFT OUTER JOIN request AS request ON employee.emp_id = request.emp_id WHERE (employee.emp_id = @emp_id) AND (request.requested_time_taken = 'FALSE') AND (request.request_end_date >= GETDATE())) AS dayspending FROM employee AS employee_1 LEFT OUTER JOIN request AS request_1 ON employee_1.emp_id = request_1.emp_id WHERE (employee_1.emp_id = @emp_id) GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial, employee_1.emp_lname
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
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".
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
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.
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])
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)
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
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>
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?
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
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
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?
I am using stored procedure to load gridview but problem is that i am not getting all rows from first table[ Subject] on applying conditions on second table[ Faculty_Subject table] ,as you can see below if i apply condition :-
Faculty_Subject.Class_Id=@Class_Id
Then i don't get all subjects from subject table, how this can be achieved.
Sql Code:- GO ALTER Proc [dbo].[SP_Get_Subjects_Faculty_Details] @Class_Id int AS BEGIN
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??
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?
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?
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:
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; }
Why does this right join return the same results as using a left (or even a full join)?There are 470 records in Account, and there are 1611 records in Contact. But any join returns 793 records.
select Contact.firstname, Contact.lastname, Account.[Account Name] from Contact right join Account on Contact.[Account Name] = Account.[Account Name] where Contact.[Account Name] = Account.[Account Name]
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.
To join the table but MUST follow the condition as bitActiv = TRUE: select emp.nvcEmpName, emp.nvcEmpAddress, ety. nvcEmployeeType from cst_EmpProfile emp left join cst_EmpType on emp.intEmployeeTypee = ety.intEmpType and emp.bitActiv = 1.
But, the sql statement doesnt output the my expected result. Because the data row return must be 1st and 2nd row as it bitActiv = true. So, how's I going achieve what i want. tq.
I need to left pad the column with 0 if it is less than 4 characters long and extract the first 2 characters on the left into a new column COUNTY_CODE.
How can I do that in transact SQL?
I tried:
SELECT RIGHT(RTRIM('0000'+ISNULL([Code],'')),4) FROM [Place] WHERE [Place Code]='B' and [Code]='627'
And I got 0627. And how do I extract the first 2 characters?
