QA Returns Different Records From SQL Server EM

Jul 20, 2005

SQL Server 7.0

If I run the following in Query Analyzer I get no records returned:

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'

If, however, I run either :

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'

or

exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '183'

I get a single record returned in each case (which is what I would
expect).

The SQL for GetLeadsOutcome_Dealer is:

CREATE PROCEDURE GetLeadsOutcome_Dealer
@FromDate smalldatetime,
@ToDate smalldatetime,
@OutcomeTypeID integer,
@DealerCode varchar(8000)
AS
DECLARE @TotalLeads integer

BEGIN
SELECT @TotalLeads=COUNT(fldLeadID) FROM tblLeads WHERE
(tblLeads.fldDealerID IS NOT NULL)


SELECT DISTINCT (dbo.tblDealers.fldDealerName),
COUNT(dbo.tblLeads.fldLeadID) as LeadCount,@TotalLeads AS TotalLeads
FROM tblLeads LEFT OUTER JOIN
tblOutcome ON tblLeads.fldOutcomeID =
tblOutcome.fldOutcomeID RIGHT OUTER JOIN
tblDealers RIGHT OUTER JOIN
tblRegion ON tblDealers.fldRegionID =
tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerID
WHERE (((dbo.tblLeads.fldEntered) BETWEEN @FromDate And @ToDate)) AND
tblOutcome.fldOutcomeID=@OutcomeTypeID AND
(dbo.tblDealers.fldDealerCode in (@DealerCode))
GROUP BY dbo.tblDealers.fldDealerName
ORDER BY dbo.tblDealers.fldDealerName

END
GO

However, if I open EM, open a table in query view, and paste this into
the SQL window,

SELECT DISTINCT tblDealers.fldDealerName, COUNT(tblLeads.fldLeadID) AS
LeadCount
FROM tblLeads LEFT OUTER JOIN
tblOutcome ON tblLeads.fldOutcomeID =
tblOutcome.fldOutcomeID RIGHT OUTER JOIN
tblDealers RIGHT OUTER JOIN
tblRegion ON tblDealers.fldRegionID =
tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerID
WHERE (tblLeads.fldEntered BETWEEN '1/1/2003' AND '12/2/2003') AND
(tblOutcome.fldOutcomeID = 10) AND (tblDealers.fldDealerCode IN (176,
183))
GROUP BY tblDealers.fldDealerName
ORDER BY tblDealers.fldDealerName

I get two records returned.

What is happening?

TIA

Edward

TABLE DEFS:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeads_tblDealers]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblLeads] DROP CONSTRAINT FK_tblLeads_tblDealers
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblSystemUsers_tblDealers]') and
OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblSystemUsers] DROP CONSTRAINT
FK_tblSystemUsers_tblDealers
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblLeadNotes_tblLeads]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblLeadNotes] DROP CONSTRAINT
FK_tblLeadNotes_tblLeads
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblDealers_tblRegion]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblDealers] DROP CONSTRAINT FK_tblDealers_tblRegion
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblDealers]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblDealers]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblLeads]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblLeads]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblOutcome]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblOutcome]
GO

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblRegion]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblRegion]
GO

CREATE TABLE [dbo].[tblDealers] (
[fldDealerID] [int] IDENTITY (1, 1) NOT NULL ,
[fldDealerCode] [varchar] (50) NOT NULL ,
[fldDealerName] [varchar] (50) NULL ,
[fldDealerTel] [varchar] (20) NULL ,
[fldDealerEmail] [varchar] (100) NULL ,
[fldDealerContact] [varchar] (50) NULL ,
[fldRegionID] [int] NULL ,
[fldDealerActive] [smallint] NOT NULL ,
[fldHeadOffice] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblLeads] (
[fldLeadID] [int] IDENTITY (1, 1) NOT NULL ,
[fldAccountNo] [varchar] (50) NULL ,
[fldDealerID] [int] NULL ,
[fldStatusID] [int] NOT NULL ,
[fldOutcomeID] [int] NOT NULL ,
[fldContacted] [smallint] NOT NULL ,
[fldDateContacted] [smalldatetime] NULL ,
[fldAppointment] [smallint] NULL ,
[fldShowRoom] [smallint] NOT NULL ,
[fldTestDrive] [smallint] NOT NULL ,
[fldSalesPersonID] [int] NULL ,
[fldCustomerName] [varchar] (50) NULL ,
[fldCHouseNo] [varchar] (50) NULL ,
[fldCStreet] [varchar] (50) NULL ,
[fldCDistrict] [varchar] (50) NULL ,
[fldCTown] [varchar] (40) NULL ,
[fldCCounty] [varchar] (50) NULL ,
[fldCPostcode] [varchar] (50) NULL ,
[fldPhoneInd] [varchar] (20) NULL ,
[fldCTel] [varchar] (50) NULL ,
[fldNewBusinessDate] [smalldatetime] NULL ,
[fldAgreementType] [varchar] (50) NULL ,
[fldCashPrice] [smallmoney] NULL ,
[fldBalanceFin] [smallmoney] NULL ,
[fldCustRate] [varchar] (10) NULL ,
[fldOrigTerm] [int] NULL ,
[fldPPPType] [varchar] (10) NULL ,
[fldBalloonValue] [smallmoney] NULL ,
[fldMonthlyInstal] [smallmoney] NULL ,
[fldRegNo] [varchar] (10) NULL ,
[fldRegDate] [smalldatetime] NULL ,
[fldModel] [varchar] (50) NULL ,
[fldDescription] [varchar] (50) NULL ,
[fldTheoPIFDate] [smalldatetime] NULL ,
[fldMonthsToGo] [int] NULL ,
[fldBalanceOS] [smallmoney] NULL ,
[fldLeadPrinted] [smallint] NULL ,
[fldMergeFile] [varchar] (255) NULL ,
[fldTPS] [varchar] (10) NULL ,
[fldMPS] [varchar] (10) NULL ,
[fldUpdated] [smalldatetime] NULL ,
[fldUpdatedBy] [int] NULL ,
[fldEntered] [smalldatetime] NULL ,
[fldReasonLeadNotProgressed] [varchar] (5000) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblOutcome] (
[fldOutcomeID] [int] IDENTITY (1, 1) NOT NULL ,
[fldOutcomeCode] [int] NULL ,
[fldOutcome] [varchar] (100) NULL ,
[fldConvertedSales] [smallint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblRegion] (
[fldRegionID] [int] IDENTITY (1, 1) NOT NULL ,
[fldRegionCode] [varchar] (10) NULL ,
[fldRegion] [varchar] (50) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD
CONSTRAINT [PK_tblDealers] PRIMARY KEY CLUSTERED
(
[fldDealerID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD
CONSTRAINT [PK_tblLeads] PRIMARY KEY CLUSTERED
(
[fldLeadID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD
CONSTRAINT [PK_tblOutcome] PRIMARY KEY CLUSTERED
(
[fldOutcomeID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD
CONSTRAINT [PK_tblArea] PRIMARY KEY CLUSTERED
(
[fldRegionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADD
CONSTRAINT [DF_tblDealers_fldActive] DEFAULT ((-1)) FOR
[fldDealerActive],
CONSTRAINT [DF_tblDealers_fldHeadOffice] DEFAULT (0) FOR
[fldHeadOffice],
CONSTRAINT [IX_tblDealers] UNIQUE NONCLUSTERED
(
[fldDealerCode]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADD
CONSTRAINT [DF_tblLeads_fldOutcomeID] DEFAULT (10) FOR
[fldOutcomeID],
CONSTRAINT [DF_tblLeads_fldContacted] DEFAULT (0) FOR [fldContacted],
CONSTRAINT [DF_tblLeads_fldAppointment] DEFAULT (0) FOR
[fldAppointment],
CONSTRAINT [DF_tblLeads_fldShowRoom] DEFAULT (0) FOR [fldShowRoom],
CONSTRAINT [DF_tblLeads_fldTestDrive] DEFAULT (0) FOR [fldTestDrive],
CONSTRAINT [DF_tblLeads_fldLeadPrinted] DEFAULT (0) FOR
[fldLeadPrinted],
CONSTRAINT [DF_tblLeads_fldTPSMatch] DEFAULT ('NO') FOR [fldTPS],
CONSTRAINT [DF_tblLeads_fldMPSMatch] DEFAULT ('NO') FOR [fldMPS]
GO

ALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADD
CONSTRAINT [DF_tblOutcome_fldConvertedSales] DEFAULT (0) FOR
[fldConvertedSales]
GO

ALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADD
CONSTRAINT [IX_tblRegion] UNIQUE NONCLUSTERED
(
[fldRegionCode]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblDealers] ADD
CONSTRAINT [FK_tblDealers_tblRegion] FOREIGN KEY
(
[fldRegionID]
) REFERENCES [dbo].[tblRegion] (
[fldRegionID]
)
GO

ALTER TABLE [dbo].[tblLeads] ADD
CONSTRAINT [FK_tblLeads_tblDealers] FOREIGN KEY
(
[fldDealerID]
) REFERENCES [dbo].[tblDealers] (
[fldDealerID]
)
GO

alter table [dbo].[tblLeads] nocheck constraint
[FK_tblLeads_tblDealers]
GO

View 2 Replies


ADVERTISEMENT

SQL Server VARCHAR(MAX) Column Returns Error While Inserting Records Into Table(ODBC Driver: SQL Native Client)

Aug 15, 2007

I created very simple table with 3 columns and one is varchar(max) datatype

When i insert records thru VC++ ADO code i am getting this error



Exception Description Multiple-step OLE DB operation generated errors. Check e
ach OLE DB status value, if available. No work was done. and Error Number:: -2147217887



ODBC Driver: SQL Native Client

SQL server 2005



Table

CREATE TABLE [dbo].[RAVI_TEMP](

[ID] [int] NULL,

[Name] [varchar](max) NULL,

[CITY] [varchar](50) NULL

)



VC++ code

#include "stdafx.h"
#include <string>
#include <strstream>
#include <iomanip>


int main(int argc, char* argv[])
{
try
{
HRESULT hr = CoInitialize(NULL);
_RecordsetPtr pExtRst = NULL;
_bstr_t bstrtDSN, bstrtSQL;
bstrtDSN = L"DSN=espinfo;UID=opsuser;PWD=opsuser;";
bstrtSQL = L"SELECT * FROM RAVI_TEMP";

_variant_t vartValueID,vartValueNAME,vartValueCITY;
_bstr_t bstrtValueID,bstrtValueNAME,bstrtValueCITY;

pExtRst.CreateInstance(__uuidof(Recordset));
hr = pExtRst->Open(bstrtSQL, bstrtDSN, adOpenDynamic, adLockOptimistic, adCmdText);

hr = pExtRst->AddNew();

bstrtValueID = L"1";
vartValueID = bstrtValueID.copy();

bstrtValueNAME = L"RAVIBABUBANDARU";
vartValueNAME = bstrtValueNAME.copy();

bstrtValueCITY = L"Santa Clara";
vartValueCITY = bstrtValueCITY.copy();

pExtRst->GetFields()->GetItem(L"ID")->Value = vartValueID;
pExtRst->GetFields()->GetItem(L"NAME")->Value = vartValueNAME;
pExtRst->GetFields()->GetItem(L"CITY")->Value = vartValueCITY;
pExtRst->Update();
pExtRst->Close();

}
catch(_com_error e)
{
printf("Exception Description %s and Error Number:: %d",(LPTSTR)e.Description(),e.Error());
return e.Error();
}
return 0;
CoUninitialize();
}


if i use regular SQL ODBC driver, no error but its truncating the data



Adv Thanks for your help

View 1 Replies View Related

Sp Returns Records In Qa, But Not In Adp

Dec 3, 2001

I have an sp (attached below) that returns records as desired when run in the query analyzer, but when run from within my adp I get only a "the sp ran successfully but did not return any records" message box. I would like to use it as a record source for a report, and when I try to do that I get a message box saying that the "Provider command for child rowset does not produce a rowset".

To repeat, the sp runs fine in QA, just not in access.

Any thoughts, anybody? Many thanks. David



Alter Procedure spDailyMicroLabel
@Date Datetime = null

AS
IF @Date IS NULL
BEGIN


SET @Date=getdate()

SELECT FERMID, fldfermtank AS "Tank", CONVERT(varchar(12), fldfilltime, 101) AS "Filled",
fldfermprod AS "Brand", CONVERT(varchar(15),fldfilltime, 108) AS "FillTime", CONVERT(varchar(12), @Date, 101) as "ReportDate",
fldfermidold AS "FermenterCode",
CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) AS "FillDay",
"Sample Test" = CASE
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int)=1
THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 3 THEN 'XTF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 4 THEN 'HLP, TF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 6 THEN 'TA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 7 THEN 'HLP/UBA, BU/Color'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 10 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 13 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 16 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 19 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 90 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 91 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 92 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 93 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 94 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 95 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 96 THEN 'HLP/UBA'
ELSE 'No Test Micro'
END
INTO #tblTempA
FROM vwUnfilteredFermenters

SELECT #tblTempA.FermenterCode AS "Fermenter ID", #tblTempA.Filled AS "Fill Date", #tblTempA.FillDay,
tblMicroTestDays.Test, tblMicroTestDays.HLPResults AS "HLP Results", tblMicroTestDays.UBAResults AS "UBA Results"
FROM tblMicroTestDays RIGHT JOIN #tblTempA ON tblMicroTestDays.TestDay = #tblTempA.FillDay


END

View 1 Replies View Related

Dataset Returns 0 Records

Feb 6, 2008

I am new to SSRS 2005 but find it to be a wonderful development tool. I discovered that when you run a report where the dataset returns no records the resulting report is blank as opposed to page header, table header, no data, and page footer. Is there a way to force these items to be displayed even when the dataset is empty? Or better yet is there a way to return a (custom) empty dataset message in place of the report body?

Thanks.

View 1 Replies View Related

SQL Call INNER JOIN Returns Too Many Records

Jan 25, 2006

I have my SQL call:
SELECT     CallLog.CallID, Journal.HEATSeqFROM         CallLog INNER JOIN                      Journal ON CallLog.CallID = Journal.CallID
There are multiple enteries in the Journal table for every entry in the CallLog table, so I receive multiple records:
CallID           HEATSeq00000164     983290904 00000164     983291548 00000164     983295209 00000231     984818271 00000231     985194317 00000231     985280248
I only want to return the LAST record in the Journal table, so the output will be:
CallID           HEATSeq00000164     983295209 00000231     985280248
Can this be done directly in the SQL call?

View 7 Replies View Related

Query Returns More Records Than Expected

Nov 29, 2012

I am having a query

select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems,
invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012'
group by INVOICE.TarrifHeadNumber

tarrifheadno SUM(INVOICEITEMS.ItemQuantity)

84195030 9.00
84198910 5.00
84212190 223.00
84569090 247.00
84799040 1138.00
8481-80-1030 137.00
85433000 6177.20

tarrifheadno is unique

Now if i use below query and add invoicetypecode field

select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems,
invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and
month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and
cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode

This query return distinct of 11 records

84195030 84195030
9.00 1
84198910 84198910
5.00 2
84212190 84212190
157.00 1
84212190 84212190
42.00 2
84212190 84212190
24.00 3
84569090 84569090
189.00 1
84569090 84569090
58.00 2
84799040 84799040
166.00 1
84799040 84799040
972.00 2
85433000 85433000
3764.00 1
85433000 85433000
2413.20 2

How to get same 7 records

View 1 Replies View Related

T-SQL (SS2K8) :: BCP Query-out Returns No Records?

Sep 3, 2014

I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.

I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:

DECLARE @strLocation VARCHAR(50)
DECLARE @TransNum VARCHAR(50)
DECLARE @strFileLocation VARCHAR(1000)
DECLARE @strFileName VARCHAR(1000)
DECLARE @bcpCommand VARCHAR(8000)

[code].....

The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:

output

NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1
NULL

If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.

I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.

View 1 Replies View Related

How Many Records A Stored Procedure Returns?

Oct 11, 2007

Hi,
I need to know whether a stored procedure returns only a single record or it can return more than one if the query if for example to return all records in a specific date range.
Thanks.

View 6 Replies View Related

IS NULL Returns Empty Records (using TEXT Type)

Mar 3, 2008

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.

View 10 Replies View Related

Results Produce A Single Record Based Off Of Parameters. Want To Change It So It Returns Multiple Records.

Dec 20, 2007

I have a query that will return one record as its results if you provide two variables: @login and @record_date. This works great if you only want one result. However, now what I want to do is not provide those variables and get the result set back for each login and record_date combination. The hitch is that there are several other variables that are built off of the two that are supplied. Here is the query:

DECLARE @login char(20), /*This sets the rep for the query.*/
@record_date datetime, /*This is the date that we want to run this for.*/
@RWPY decimal(18,2), /*This is the required wins per year.*/
@OCPW decimal(18,2), /*This is the opportunities closed per week.*/
@OACW decimal(18,2), /*This is opportunities advanced to close per week.*/
@TOC decimal(18,2), /*This is the total number of opportunities in close.*/
@OANW decimal(18,2), /*This is opportunities advanced to negotiate per week.*/
@TON decimal(18,2), /*This is the total number of opportunities in negotiate.*/
@OADW decimal(18,2), /*This is the opportunities advanced to demonstrate per week*/
@TOD decimal(18,2), /*This is the total number of opportunities in demonstrate.*/
@OAIW decimal(18,2), /*This is the opportunities advanced to interview per week.*/
@TOI decimal(18,2), /*This is the total number of opportunities in interview.*/
@OCW decimal(18,2), /*This is the opportunities created per week.*/
@TOA decimal(18,2) /*This is the total number of opportunities in approach.*/

SET @login = 'GREP'
SET @record_date = '12/18/2007'
SET @RWPY = (SELECT ((SELECT annual_quota FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)/(SELECT target_deal FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)))
SET @OCPW = (SELECT @RWPY/weeks FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OACW = (SELECT @OCPW/cls_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOC = (SELECT @OACW*(cls_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OANW = (SELECT @OACW/neg_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TON = (SELECT @OANW*(neg_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OADW = (SELECT @OANW/dem_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOD = (SELECT @OADW*(dem_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OAIW = (SELECT @OADW/int_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOI = (SELECT @OAIW*(int_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @OCW = (SELECT @OAIW/app_perc_adv FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)
SET @TOA = (SELECT @OCW*(app_time/7) FROM #pipelinehist WHERE loginname = @login AND record_date = @record_date)

SELECT loginname,
CAST(@TOA AS decimal(18,1)) AS [Opps in Approach],
app_time AS [Approach Average Time],
app_perc_adv AS [Approach Perc Adv],
CAST(@TOI AS decimal(18,1)) AS [Opps in Interview],
int_time AS [Interview Average Time],
int_perc_adv AS [Interview Perc Adv],
CAST(@TOD AS decimal(18,1)) AS [Opps in Demonstrate],
dem_time AS [Demonstrate Average Time],
dem_perc_adv AS [Demonstrate Perc Adv],
CAST(@TON AS decimal(18,1)) AS [Opps in Negotiate],
neg_time AS [Negotiate Average Time],
neg_perc_adv AS [Negotiate Perc Adv],
CAST(@TOC AS decimal(18,1)) AS [Opps In Close],
cls_time AS [Close Average Time],
cls_perc_adv AS [Close Perc Adv]
FROM #pipelinehist
WHERE loginname = @login AND record_date = @record_date

Here is some sample data to use with this. With this sample data what I want to get back is a total of 30 records in the result set each with its data specific to the login and record_date of that returned record.

CREATE TABLE #pipelinehist (
glusftboid int IDENTITY(1,1) NOT NULL,
record_date datetime NOT NULL,
loginname char(20) NOT NULL,
app_new float NOT NULL,
app_time float NOT NULL,
app_perc_adv float NOT NULL,
int_time float NOT NULL,
int_perc_adv float NOT NULL,
dem_time float NOT NULL,
dem_perc_adv float NOT NULL,
neg_time float NOT NULL,
neg_perc_adv float NOT NULL,
cls_time float NOT NULL,
cls_perc_adv float NOT NULL,
target_deal money NOT NULL,
annual_quota money NOT NULL,
weeks int NOT NULL
) ON [PRIMARY]

INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'AREP', 56.8, 26.9, 0.57, 29.5, 0.47, 20, 0.67, 80.7, 0.53, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'BREP', 33.2, 0.5, 0.9, 7.7, 0.77, 8, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'CREP', 210.2, 0.3, 0.87, 6.6, 0.5, 13.7, 0.4, 16.3, 0.43, 1.5, 0.91, 461.25, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'DREP', 47.6, 5, 0.53, 33.3, 0.6, 57.5, 0.53, 50, 0.7, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'EREP', 75.3, 110.9, 0.47, 36, 0.5, 17.4, 0.87, 20.3, 0.6, 7.2, 0.83, 2021.74, 775000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'FREP', 17.2, 23.3, 0.73, 6.8, 0.8, 6.3, 0.93, 29.7, 0.67, 15.5, 0.83, 2218.95, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'GREP', 105.4, 67, 0.2, 32.9, 0.43, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'HREP', 116.4, 118.5, 0.33, 30.9, 0.77, 46.3, 0.77, 46.3, 0.6, 0.9, 0.97, 1735.13, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'IREP', 143.3, 9, 0.77, 96, 0.17, 21.6, 0.77, 39.9, 0.43, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 67.6, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'KREP', 107.6, 38.2, 0.23, 47.5, 0.47, 21.3, 0.77, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'LREP', 18.6, 8.3, 0.87, 23.2, 0.57, 2.6, 0.87, 12.2, 0.67, 1, 1, 1229.02, 330000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'MREP', 4, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.4, 1, 1091.22, 350000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'NREP', 54, 21.6, 0.57, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/17/2007 0:00', 'OREP', 37.6, 24.4, 0.57, 50.1, 0.43, 6.7, 0.87, 15.6, 0.73, 0.9, 0.97, 1163.48, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'AREP', 57.2, 32.5, 0.6, 29.5, 0.47, 20, 0.67, 85.6, 0.5, 2.1, 0.97, 2194.93, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'BREP', 33.9, 0.5, 0.93, 7.8, 0.73, 8.3, 0.77, 9.2, 0.6, 7.7, 0.64, 971.1, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'CREP', 152.1, 0, 0.87, 4.3, 0.67, 9.7, 0.47, 15.7, 0.47, 1.8, 0.85, 396.43, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'DREP', 80.5, 9.8, 0.5, 40.7, 0.57, 68.3, 0.43, 64.2, 0.57, 1.5, 1, 2045.7, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'EREP', 61, 92.1, 0.5, 31, 0.53, 16.9, 0.83, 17.7, 0.6, 7.3, 0.83, 2318.04, 775000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'FREP', 19.4, 21.1, 0.7, 5.3, 0.77, 2.2, 0.93, 33.3, 0.7, 9.7, 0.87, 1937.17, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'GREP', 81.7, 40.5, 0.3, 33, 0.37, 18.5, 0.67, 8.9, 0.77, 3.5, 0.93, 1838.91, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'HREP', 128.6, 115.7, 0.3, 30.9, 0.77, 46.3, 0.77, 48.8, 0.6, 0.9, 0.97, 1728.29, 1150000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'IREP', 100.9, 3.4, 0.77, 86.2, 0.27, 18, 0.8, 54.7, 0.37, 0.9, 0.93, 1385.43, 400000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'JREP', 179.4, 66.7, 0.7, 63.5, 0.1, 41.4, 0.6, 20.2, 0.8, 14, 0.7, 1563.76, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'KREP', 285.2, 36.5, 0.1, 46, 0.43, 24.2, 0.73, 9.6, 0.73, 2.1, 0.83, 2120, 575000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'LREP', 17.6, 7.3, 0.9, 21.5, 0.57, 1.7, 0.87, 12.2, 0.67, 1, 1, 1250.54, 330000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'MREP', 26.7, 46.2, 0.6, 26.7, 0.57, 8.1, 0.87, 1.7, 0.9, 1.3, 1, 979.7, 350000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'NREP', 61.6, 20.8, 0.5, 1.7, 0.77, 11, 0.8, 7.4, 0.9, 49, 0.47, 3240.68, 1300000, 50)
INSERT into #pipelinehist VALUES ('12/18/2007 0:00', 'OREP', 31.6, 16.9, 0.63, 50.1, 0.43, 7.2, 0.87, 19.5, 0.7, 0.9, 0.97, 1303.48, 330000, 50)

View 3 Replies View Related

Sql Server Function Returns Xml String

Oct 21, 2005

Hi, How to write a SQL function that returns a string that contains xml string from "SELECT ....FOR XML"In other word, I want to put result of select .. for xml into a variable.Thanks

View 3 Replies View Related

SQL Server 2008 :: Parent Records Ordering And Display Child Records Next To It?

Sep 7, 2015

declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2

[code]....

This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....

Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending

--Final output should be

PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4

View 2 Replies View Related

I Want To Transfer ONLY New Records AND Update Any Modified Records From Oracle Into SQL Server Using DTS

Jul 23, 2005

I need a little help here..I want to transfer ONLY new records AND update any modified recordsfrom Oracle into SQL Server using DTS. How should I go about it?a) how do I use global variable to get max date.Where and what DTS task should I use to complete the job? Data DrivenQuery? Transform data task? How ? can u give me samples. Perhaps youcan email me the Demo Package as well.b) so far, what I did was,- I have datemodified field in my Oracle table so that I can comparewith datelastrun of my DTS package to get new records- records in Oracle having datemodified >Max(datelastrun), and transferto SQL Server table.Now, I am stuck as to where should I proceed - how can I transfer theserecords?Hope u can give me some lights. Thank you in advance.

View 2 Replies View Related

OLAP Server Returns Incorrect Data

May 24, 2001

I'm working with PivotTable on Excel 2000 which is connected to an OLAP server (from SQL Server 7 installation). The pivot is intended to analyze Sales during April 2001. Yesterday I found out that OLAP/Excel returned/displayed inconsistent data. The 'April Total' value is NOT equal to the 'Quarter 2 Total' (I already inspected the underlying database and sure that there is absolutely NO data for months after April 2001). The value for 'April Total' is the correct one. I'm not sure whether the problem resides on the OLAP Server or Excel (pivot) itself. For ones who like to help me I would be glad to supply you with the screenshots (just email me).
Please help.

Regards

Dave

View 1 Replies View Related

SUM Returns Incorrect Value In SQL Server Mobile Query ... Bug??

Feb 6, 2007

Hi all,

I am a newcomer to Microsoft Database Technology and have appeared to come across an issue with the SUM function in SQL Server Mobile Edition.

I am running Visual Studio 2005 and have created 2 tables:

Orders and OrderLines which are set up in master detail fashion.

The SQL Statement I create in the Query Builder is as follows:


SELECT     Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address, SUM(OrderLines.Quantity * OrderLines.QualityReference) AS Total
FROM         Orders, OrderLines
WHERE     Orders.OrderNo = OrderLines.OrderNo
GROUP BY Orders.OrderNo, Orders.OrderDate, Orders.Priority, Orders.Address

Now, the SUM returns a total for all records in the OrderLines table, and not just the records whose OrderNo is the same as Orders.OrderNo

Can someone out there please clarify whether its an issue with my code or a bug with SQL Server Mobile???

Here are a couple of screenshots which will demonstrate what I mean.

Here is the contents of the Orders and OrderLines tables.  Each order has only 1 line item in it.

 http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceordersdata.html

When performing the summation over the OrderLines table, it produces the SUM of the all records in OrderLines and not according to the GROUP BY clause. See the following screen shot.

http://public.fotki.com/AussieNoobie/sqlerrors/vs2005sqlceerror1.html

I hope this explains it better.

Anyone have any ideas??? 

Thanks in advance

 

View 13 Replies View Related

Xp_AvailableMedia Returns 0 Rows In SQL Server 2005

May 2, 2007

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?



Thanks in advance for your help.



Frank



View 3 Replies View Related

Select For Cursor Returns 0 Same Select Highlighted Returns 2,000+

Jul 23, 2005

Grrr!I'm trying to run a script:print 'Declaring cursor'declare cInv cursor forward_only static forselectdistinctinv.company,inv.contact,inv.address1,inv.city,inv.state,inv.postalcode,inv.cmcompanyidfromdedupe.dbo.ln_invoice as invleft joindedupe.dbo.customerid as cidondbo.fnCleanString(inv.company) = cid.searchcowhere((inv.customerid is nulland cid.searchco is null)and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' andinv.outstanding > 0.01))and not inv.company is null)print 'Cursor declared'declare@contact varchar(75),@company varchar(50),@address1 varchar(75),@city varchar(30),@state varchar(20),@zip varchar(10),@cmcompanyid varchar(32),@iCount int,@FetchString varchar(512)open cInvprint 'cursor opened'fetch cInv into@company,@contact,@address1,@city,@state,@zip,@cmc ompanyidprint 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows asvarchar(5))All the prints are there to help me figure out what's going on!When I get to the Print 'Cursor fetched @@cursor_rows....the value is 0 and the script skips down to the close and deallocate.BUT, if I just highlight the Select...When section, I get over 2,000rows. What am I missing?Thanks.

View 6 Replies View Related

Sql Server Reporting Services 2005 Returns 404 Not Found

Oct 14, 2007

I just installed Sql Server Reporting Services 2005 on a Windows Server 2003 R2 x64 server. I configured Reporting Services through the Reporting Services Configuration tool and everything has a green checkmark.

I'm trying to connect to the webservice, but keep getting 404 not found errors. I've tried putting the following into a web browser and all return a 404 error:

http://msql01/reports/
http://msql01/reportserver/
http://msql01/reportserver/reportservice.asmx?wsdl
http://msql01/reportserver/reportservice2005.asmx?wsdl

I've tried edting the RSWebApplication.config file using the following configurations (restarting IIS afterwards) without any luck:
<UI>
<ReportServerUrl></ReportServerUrl>
<ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>

and

<UI>
<ReportServerUrl>http://msql01/ReportServer/</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>

Any help is appreciated!

Thanks,
Rob

View 10 Replies View Related

SQL Server 2012 :: Import XML Returns 0 Rows Affected Failing

Nov 29, 2013

I am trying to do a bulk import of data from XML into sQL.

My query returns no errors but no data gets imported.

Here is my XML

?xml version="1.0" encoding="utf-8"?>
<status>
<connection_status>successful</connection_status>
<operation_status>successful</operation_status>
<CustomerDeposits>

[code]....

View 2 Replies View Related

SQL Server 2008 :: Query That Returns Only Rows That Meet Specific Condition?

Oct 22, 2015

Here's what my table looks like;

UniqID | Code |

1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC

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.

View 9 Replies View Related

After Instalation The Report Server Returns:Attempted To Load A 64-bit Assembly On A 32-bit Platform

Jun 19, 2007

Our server is 64 bits OS. After SQL instalation we try to load the Reports web site and the server return the following error, can anyone help us?:



Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.Server
Error in '/Reports' Application.


Attempted to load a 64-bit assembly on a 32-bit platform. Use
ReflectionOnlyLoad() instead if trying to load for reflection purposes.
Description: An unhandled exception occurred during the execution of the current
web request. Please review the stack trace for more information about the error
and where it originated in the code.

Exception Details: System.BadImageFormatException: Attempted to load a 64-bit
assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to
load for reflection purposes.

Source Error:

An unhandled exception was generated during the execution of the current
web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.

Stack Trace:


[BadImageFormatException: Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) +0
System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +211
System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) +141
System.Reflection.Assembly.Load(String assemblyString) +25
System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +32

[ConfigurationErrorsException: Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Web.Configuration.CompilationSection.LoadAssemblyHelper(String assemblyName, Boolean starDirective) +596
System.Web.Configuration.CompilationSection.LoadAllAssembliesFromAppDomainBinDirectory() +3479065
System.Web.Configuration.CompilationSection.LoadAssembly(AssemblyInfo ai) +46
System.Web.Compilation.BuildManager.GetReferencedAssemblies(CompilationSection compConfig) +177
System.Web.Compilation.BuildProvidersCompiler..ctor(VirtualPath configPath, Boolean supportLocalization, String outputAssemblyName) +180
System.Web.Compilation.ApplicationBuildProvider.GetGlobalAsaxBuildResult(Boolean isPrecompiledApp) +3446645
System.Web.Compilation.BuildManager.CompileGlobalAsax() +51
System.Web.Compilation.BuildManager.EnsureTopLevelFilesCompiled() +462

[HttpException (0x80004005): Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Web.Compilation.BuildManager.ReportTopLevelCompilationException() +57
System.Web.Compilation.BuildManager.EnsureTopLevelFilesCompiled() +612
System.Web.Hosting.HostingEnvironment.Initialize(ApplicationManager appManager, IApplicationHost appHost, IConfigMapPathFactory configMapPathFactory, HostingEnvironmentParameters hostingParameters) +456

[HttpException (0x80004005): Attempted to load a 64-bit assembly on a 32-bit platform. Use ReflectionOnlyLoad() instead if trying to load for reflection purposes.]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +3426871
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +88
System.Web.HttpRuntime.ProcessRequestInternal(HttpWorkerRequest wr) +149




Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET
Version:2.0.50727.42

View 1 Replies View Related

Iseries To Sql Server 2005 Package Returns Invalid Data Type

May 3, 2006

I have installed the trial enterprise edition of sql server 2005. I am trying to recreate a dts packages. I am using an iseries OLE DB provider. In the data flow the connection to a physical file that has string data gets translated into binary data. Has any one gotten a download to work with the correct data?

View 1 Replies View Related

SQL 2012 :: Query To Make Single Records From Multiple Records Based On Different Fields Of Different Records?

Mar 20, 2014

writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.

ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29

output should be ......

ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29

View 0 Replies View Related

SQL Server 2012 :: List Catalog Reports With No Execution History Returns 0 Rows

Feb 13, 2015

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'

View 4 Replies View Related

Reporting Services :: Application In Sharepoint 2013 Returns Internal Server Error

Jan 14, 2013

I Installed SQL Reporting services 2012 service with sharepoint 2013 ... and started it with success..I followed the microsoft's procedure.. URL....After , I have not encountered any problems when I created the application  <--- 3 databases created in my SQLSERVER instance//The problem occured when I tried to click on my ssrs application_name --> system settings...ERROR 500 Correlation ID: f6c5f49b-6ab0-4009-6f31-fd019ddc3ecc

This what I read in the log of sharepoint -->

01/14/2013 12:18:36.22     w3wp.exe (0x0BEC)                           0x05D8    SharePoint Foundation       
     Monitoring                        nasq    Medium      Entering monitored scope (Request
(GET:http://vwaisql201202:22628/_admin/ReportServer/ReportingServicesSystemSettings.aspx?ID=5abbbf0d-e8dd-4e8e-93d6-eddc77225a9f)). Parent No    

[code]....

View 7 Replies View Related

SQL Server 2014 :: Selecting Records From Table 2 While Counting Records In Table 1

Aug 11, 2015

Table1 contains fields Groupid, UserName,Category, Dimension

Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)

So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)

In Table1 There might be 10 Groupid records all of which are different.

View 9 Replies View Related

Execute Sql Server Agent Job Task - Job Immediately Returns Success... However Agent Job Is Still Running???

Nov 30, 2006

when I run a package from a command window using dtexec, the job immediately says success.
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 3:37:41 PM
Finished: 3:37:43 PM
Elapsed: 2.719 seconds



However the Job is still in th agent and the status is executing. The implications of this are not good. Is this how the sql server agent job task is supposed to work by design.



Thanks,

Larry

View 1 Replies View Related

SQL Server 2000 - Issue W/ UPDATE - Single Row Update Returns 2 Different Messages

Nov 11, 2007

I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000.  I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process.   Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue.  In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine. 
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically).  When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense.  There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks 
 

View 2 Replies View Related

SQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?

Mar 25, 2015

I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).

I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.

Right now, I'm doing it this way.

DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId

[Code] .....

This works; it just seems cludgy. Is there a more elegant way to do this?

View 4 Replies View Related

Odbc - Binding Sql Server Binary Field To A Wide Char Field Only Returns 1/2 The Daat

Jul 23, 2005

Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn

View 2 Replies View Related

SQL Server 2008 :: Loop Through Date Time Records To Find A Match From Multiple Other Date Time Records?

Aug 5, 2015

I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.

E.g.

Query 1

Select ID, Person, ProposedEvent, DayField, TimeField
from MyOptions
where person = 'me'

Table

Select Person, ExistingEvent, DayField, TimeField
from MyTimetable
where person ='me'

Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.

I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.

View 5 Replies View Related

Why This SP Never Returns -2?

Dec 16, 2004

CREATE PROCEDURE UpdateAccount
(
@AccountID int,
@EmailAddress varchar(255),
@Password varchar(16)
)
AS
DECLARE @actualPassword varchar(16)
SELECT @actualPassword = Password
FROM Account WHERE AccountID = @AccountID

If Exists( SELECT EmailAddress FROM Account
WHERE EmailAddress = @EmailAddress )
RETURN -1

ELSE IF @Password <> @actualPassword
RETURN - 2

ELSE
UPDATE Account
SET
EmailAddress = @EmailAddress,
Password = @Password
WHERE AccountID = @AccountID
GO

View 2 Replies View Related

Deleting Old Records Is Blocking Updating Latest Records On Highly Transactional Table

Mar 18, 2014

I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.

In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously

While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.

Is there any SQL Server hints to avoid blocking ..

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved