I am trying to add to a query to produce a report in Apex. Currently I have this
SELECT
RE_EZPAY_AT_AGENCY_DAILY.WAITING_TO_BILL AS "WTB",
VE_MEMBER.TITLE,
VE_MEMBER.FIRSTNAME,
VE_MEMBER.SURNAME,
VE_MEMBER.ADDRESS_1,
[Code] ....
I want to add 'OR FOLLOW_UP_DATE <= SYSDATE'. How do I add another OR statement?
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
Hi Everyone:
*Before* I actually call up Microsoft SQL Customer Support Services and ask them, I wanted to ping other people to see if you have ever ran into this exact error
"Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I would have searched the forums myself, but at this moment in time, search is broken :(
If anyone has run into this error before, what conditions would exist that this could happen? That is, if I can sniff this out with suggestions from the community, I would be happy to do so.
It is an oddity because if I alter a couple subqueries in the where clause [ i.e., where tab.Col = (select val from tab2 where id='122') ]to not have subqueries [hand coded values], then the t-sql result is fine. It's not as if subqueries are oddities... I've used them when appropriate.
fwiw - Not a newbie t-sql guy. ISV working almost daily with t-sql since MS SQL 2000. I have never seen this message before...at least I don't recall ever seeing it.
Thanks in advance for other suggested examination paths.
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days
PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days
PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
join three tables and wont be duplicate records.
I have tried and attached the computed results and also expecting results.
IF OBJECT_ID('tempdb..#tmpExam1')IS NOT NULL DROP TABLE #tmpExam1
IF OBJECT_ID('tempdb..#tmpExam2')IS NOT NULL DROP TABLE #tmpExam2
IF OBJECT_ID('tempdb..#tmpExam3')IS NOT NULL DROP TABLE #tmpExam3
[Code]....
I need a query to produce permutation combination.
declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100))
insert into @t1 values ('I')
insert into @t1 values ('U') from the above I need following output (attached output),
Aim –To produce a unique line by month for the FDMSAccountNo
I have created the following query (see below) which produces the following result set
FDMSAccountNo AvgValCard_TypePence_Percentfee_wholesale_date BegMonthDate
8780000018820.012900000CreditPercent2009-07-082009-07-01 00:00:00.000
8780000018820.015700000CreditPercent2011-02-242011-02-01 00:00:00.000
8780000018820.014700000CreditPercent
2011-02-252011-02-01 00:00:00.000
SELECT
[FDMSAccountNo],
AVG([fee_retail_amt]*1.0) AS AvgVal,
Card_Type,
[Pence_Percent],
[fee_wholesale_date],
DATEADD(mm,DATEDIFF(mm,0,[fee_wholesale_date]),0) AS BegMonthDate
[Code] ....
Desired results
FDMSAccountNoAvgValCard_TypePence_Percentfee_wholesale_date
8780000018820.012900000CreditPercent2009-07-08
8780000018820.012900000CreditPercent2009-08-08
8780000018820.012900000CreditPercent2009-09-08
8780000018820.012900000CreditPercent2009-10-08
8780000018820.012900000CreditPercent2009-11-08
[Code] ....
How I can get the desired result using query. I don't want any
'@' variable in the result.
DECLARE @ST AS varchar(10)='AA'
DECLARE @desc AS int=8
DECLARE @STID AS int=4
DECLARE @PP AS int=63
DECLARE @SS AS int=22
/* Desired Result */
Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
Please help!!!!
Need to produce a query that show the cities and the number of people per city?
simple table
firstname
lastname
age
location
location shows the dif cities and the sql query will show cities will a count of people per city
how do you do this?
I'm using an SqlDependency so I'll get an alert when the data under a query changes. When the query does not meet the criteria spelled out here http://msdn2.microsoft.com/en-us/library/aewzkxxh.aspx then I immediately get a SqlNotificationEventArgs event with the "Invalid" note. This is good.
I would like to know that a query is invalid before I try it with my ASP.NET application. Is there a sproc I can give to my SQL developers so they can test that they haven't used any stuff that would invalidate SqlDependency? What I'm hoping is that there's already a system sproc which could be called...
exec sp_QueryInvalidForNotification "exec myTestSproc 99"
Does such a sproc exist? Or does anyone know how to write one?
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
hi
is there any feature in MSSql that produce SQL statement for a database(include CREATE TABLE, INSERT Records, ...)
How would I write a query to produce all combinations for a situation such as the following?
Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.
Adjective table looks like e.g.
ID Adj
1 good
2 so so
3 bad
Noun table looks like e.g.
ID Noun
1 apple
2 orange
3 banana
And the result set would look like
This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.
I would take a stab at this myself and post even something that doesn't work...
Will osql product an SQL backup? By this I mean a text file with thetransact-sql in it so I can use it to recreate the database elsewhere?Thank you.
View 1 Replies View RelatedI'm embarassed I haven't figured this out already but here goes.
Lets say you need a percentage from dividing two integers for example
"select 2000/4000"
This will produce a zero and I'm assuming that is because of the datatypes involved (the values are coming from columns where the datatype is int)
I've tried converting the values to decimal types but I keep getting overflow erros unless I use very small values.
As always, thanks VERY MUCH for the kind advice.
way to insert a summary row where there are no query results - in effect a row to say there were no results for today. I am using SQL Server 2008.
I have created a report which uses 2 tables to store all daily transactions & a one row summary of those daily transactions for upto 4 countries - both tables are updated nightly by 2 SP and the unique identifier is a field called ID which contains the country code and date;
table1_detail;
store all daily transactions for 4 countries and the unique identifier is an ID which stores the country code and date (XX_ddmmyy) eg GB_280513. There may be transactions for upto 4 countries(GB/FR/IE/DE) per day or there may be none.
Table1:
Code:
ID DATE CUST VALUE ERROR ...etc
GB_280513 280513 101 10.50 YES
GB_280513 280513 102 90.00 NO
FR_280513 280513 201 25.00 NO
IE_280513 280513 301 60.00 NO
FR_280513 280513 202 10.50 YES
FR_280513 280513 203 10.50 NO
GB_280513 280513 103 20.00 YES
GB_280513 280513 104 5.00 YES
table2_summary;
summary of daily transactions per unique identifier (XX_ddmmyy) in table1_detail. When there are transactions per unique identifier in table1 the SP inserts a row summarising quantity, value, error count etc.
Table2:
Code:
ID DATE NO OF TRANS VALUE NO OF ERROR ...etc
GB_280513 280513 4 125.50 3
FR_280513 280513 3 46.00 1
IE_280513 280513 1 60.00 0
I need the insert into table2_summary to be able to insert a row everyday for each country showing zeros where there were no transactions for that day (ID) in table1;
Table2 expected results:
Code:
ID DATE NO OF TRANS VALUE NO OF ERROR ...etc
GB_280513 280513 4 125.50 3
FR_280513 280513 3 46.00 1
IE_280513 280513 1 60.00 0
DE_280513 280513 0 0.00 0
When there are no transactions in table1 for a day (ID) then the summary needs 4 zero rows inserted.
hi,
iam having some strange problem. iam searching for records between from date and todate. this query works well except these conditions
a) if i give input say fromdate-2/2/2007 and todate-8/2/2007, the query goes for infinite execution time(pls note the difference between twodates is less than or equal to 6 months)
b)whereas if i give fromdate-2/2/2007 and todate-10/2/2007, the query works fine.(more than 6 months interval) seems strange for me.
pls if anyone came across these kind of errors and solved pls help me.
Thanks in advance
Hi!
I'm quite new to T-SQL and is about to build a small reporting db using SQL.
Most of the data I can move with normal INSERT INTO ... SELECT, but there are some tables that I want to
produce using T-SQL. For example I want to build a Date table like..
Date
Year
Quarter
Month
WeekDay
...
With some precalculated values for each date.
I've searched the forum but have not found any information on how to produce table contents in a good manner. I would appreciate if someone would have the time to point me in the right direction. I do not want to do this by code in my application.
My first thought is to use some kind of Insert Cursor in a While loop...
Pseudo:
---------------------
declare cursor ex for 'Insert table ... '
while
begin
(produce data)
insert data
end
close cursor
---------------------
While browsing the net I've got the feeling that you use cursor less in SQL Server than in other db-engines...
Have a nice day!
Hello everybody!
trying to do the following:
-create a report in access project
-got 3 stored procedures which return data that shall be shown on report
-need one recordset as datasource (or can i use more than one here?)
Problem:
Data was unrelated before, now needs to be on same report, that's why until now i have 3 different pretty complex stored procedures returning a recordset each.
I could of course copy and paste the whole 3 into 1 new stored proc, but when one changes i had to change the newly created one too (which might get messy when doing a lot of maintenance and changes on the others)
Can create a stored procedure that simply integrates those 3 into one recordset something like this (in pseudo-code):
CREATE PROCEDURE IntegrateSPs AS
INTEGRATE
SP1,SP2,SP3
INTO myRecordset
Anything like this possible?
thx in advance,
Kuma
I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).
I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..
Here is query #1:
select investor, full_date, month_end_summary, category, loan_count
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) < 6
group by investor, full_date, month_end_summary, category, loan_count
order by investor, full_date
Here is query #2:
select investor, full_date, month_end_summary
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) =0
order by investor, full_date
Can they be combined into one query to produce one result table??
I have a SQL database that has multiple tables I need to combine for an analytics presentation. The tables are PRODUCTS (contains product information and UPC), STORES (Store number, name and address), DATES (List of dates that data is published on), and DATA (quantity, by store, by UPC, by date). I am trying to combine these so that i have one table with a row for each UPC on each day at each store. Also, I would like to lookup values for year ago quantities at the same store for the same UPC. Is there a way to structure a query to provide all of this information? I know it will be a lot of rows, but I will filter by product prior to pulling the data.
I think the solution requires a subquery in the FROM portion of the statement, but I am not sure how to structure it. Here are the Fields:
Products.UPC, Data.UPC, Data.Store_Num, Stores.StoreNum, Data.StartDate, Dates.DateKey, and finally the reason we are here Data.Qty
I am using an Azure SQL Database.
Deaa group,I am using SQLServer 2000 in an XP Sp2. I would like to do thefollowing:I have a program running on a database server that generates some datawhich are loaded to the database. This program is used in a webapplication, invoked by some java program and JSP scripts. (I amfrontend illiterated.)The question is, is it possible to write a stored procedure to generateoutput in excel spreadsheet? So that user could call this procedureand get spreadsheet output on the client side.Any pointer to a solution would be immensely apprecaited.thanks,charia
View 2 Replies View Relatedhi all, have anyone else met with the following problem.we've got a SQL2000SP3a on a 2 active-active node clusters of W2K3,this cluster in using AD.I've found that whenever I use Enterprise Manager on my local computerto register the above instance, it will constantly produce "Failedlogin for <Domain><DomainUser>" errors in the Windows Security Log ofthe database server machine. There's no failed login in the SQL errorlog and I can work normally with the Enterprise Manager. The OS failedlogins error are gone once I un-register the instance from myEnterprise Manager.Does anyone know or met with this before?
View 1 Replies View RelatedWe have a SQL 2K5 10GB database that, as part of the recovery plan, gets a differential backup every six hours. Log file backups occur every hour, and a full backup is done every 24 hours. Over the weekend, the differential backup produced a 55GB backup file which caused us a lot of issues besides disk space usage (log backups couldnt finish, mirroring broke, etc.). This is also the max growth size that the log file is set to. There are no errors in the ERRORLOG, or in the job history. It's as if the backup was successful, which I assume it was, but the file was sparse.
I should mention that our full backup is typically 10GB, log file backups are typically 100 to 500MB, and the diff backup is generally 1GB to 3GB.
Has anyone experienced this issue before?
What causes it?
How do we resolve it?
Thank you in advance for your help,
Greg
and how can it be done?
View 1 Replies View RelatedI am having problems building a CTE. I have a table:
declare @tbl table ( ID int, S time(0), E time(0) )
with the following data:
insert@tbl ( ID, S, E )
values ( 1, '03:00', '05:00' ),
( 1, '04:00', '06:30' ),
( 1, '22:00', '23:00' ),
( 1, '23:00', '23:40' ),
( 1, '23:30', '23:45' )
I need to build a CTE that will eliminate overlaps and produce the following result:
ID S E
-- --------- ---------
1 03:00:00 06:30:00
1 22:00:00 23:45:00
create table #t1 (id int)
create table #t2 (id int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t2 values (1)
insert into #t2 values (2)
Run the below quires, you will get 2 different outputs.Second is the desired output. I cant find reason for query1
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id and b.id is null
-- Query1
select * from #t1 a left join #t2 b on a.id = b.id where b.id is null
Hi,
I made a simple service broker code that is:
-- step 1: Enable Broker
use master
alter database AdventureWorks
set enable_broker
go
-- step 2: prepare
use AdventureWorks
go
create schema CustomerService
go
create schema EmailService
go
-- step 3: Message Type
create message type [//AdventureWorks/Sales/CustomerDetails]
validation = well_formed_xml
go
-- step 4: Contract
create contract [//AdventureWorks/Sales/SendCustomerDetails]
(
[//AdventureWorks/Sales/CustomerDetails] sent by initiator
)
go
-- step 5: Queue
create queue CustomerService.NewCustomerQueue
go
create queue EmailService.NewCustomerEmailQueue
go
-- step 6: Service
create service [//AdventureWorks/Sales/CustomerService]
on queue CustomerService.NewCustomerQueue
go
create service [//AdventureWorks/Sales/EmailService]
on queue EmailService.NewCustomerEmailQueue
(
[//AdventureWorks/Sales/SendCustomerDetails]
)
go
-- step 7:
create procedure CustomerService.proc_SendMessage
(
@firstName nvarchar(50)
,@lastName nvarchar(50)
,@emailAddress nvarchar(200)
)
as
-- sending a message
declare @message nvarchar(max)
set @message = nchar(0xFEFF)
+ '<Customer>'
+ '<CustomerName>' + @firstName + ' ' + @lastName + '</CustomerName>'
+ '<EmailAddress>' + @emailAddress + '</EmailAddress>'
+ '</Customer>'
declare @conversationHandle uniqueidentifier
begin dialog conversation @conversationHandle
from service [//AdventureWorks/Sales/CustomerService]
to service '//AdventureWorks/Sales/EmailService'
on contract [//AdventureWorks/Sales/SendCustomerDetails]
;send on conversation @conversationHandle
message type [//AdventureWorks/Sales/CustomerDetails]
(
@message
)
go
create procedure CustomerService.proc_ReceiveMessage
as
-- receiving a message
declare @conversationHandle uniqueidentifier, @messageTypeName nvarchar(256)
;receive top(1)
@conversationHandle = conversation_handle
,@messageTypeName = message_type_name
from CustomerService.NewCustomerQueue
if @@rowcount = 0
begin
print 'No further message found in CustomerService.NewCustomerQueue'
end
go
exec CustomerService.proc_SendMessage 'Bishoy','George','bishoy@bishoy.com'
exec CustomerService.proc_ReceiveMessage
--------------------------------------------------------------------------------------------------------------------------
Now the problem is:
when I execute the 2 procedures I receive:
(0 row(s) affected)
No further message found in CustomerService.NewCustomerQueue
Why doesn't the broker send the message?
Is there any needed preparing should I have done first?
Thank you and best regards.
I am rewriting several stored procedures that originally had lots of "multiplicated" code. I am aware that references to objects within dynamic SQL do not create dependencies, so I intend to add code that will generate the dependencies but will NOT produce any output in both the Results and Messages tabs, not be overly "messy" or complicated, and have the least impact on execution plan creation as possible.
As we use a dependency list of tables used to our support staff pinpoint possible data issues associated with each of these stored procedures.
I have tried a few methods already, including this:
SET @SQL = N'SELECT Column1,Column2 FROM dbo.TableName';
...
/***************************************************************************************/
/* This code block is only to establish dependency of objects used within dynamic SQL. */
/* */
/* SET statements are used so that no output is produced in Results or Messages tabs. */
/* Object existence check avoids error 208, "Invalid object name" message. */
/***************************************************************************************/
DECLARE @DependentObject SQL_VARIANT;
IF OBJECT_ID(N'dbo.TableName', N'U') IS NOT NULL
SET @DependentObject = ( SELECT TOP (1) Column1,Column2 FROM dbo.TableName);
/* End code for dependency of objects used within dynamic SQL */
Hi All,
I have developed few charts - line graphs - and have checked the Numeric or time-scale values on the X-Axis. Now, when the graphs come out, they appear to have two datasets. For example, if the X axis has values from 1st march to 14th March, then there will be a line from 1st March to 14th March, but additionally there will be another one, pointing to different datapoint, and again starts from 1st March and ends at 14th March. Whereas I think it is because of the Numeric or time-scale values selection, but I am not sure. Also, is there a particular way to interpret such graphs??
Thanks a lot, I would have loved to give a picture, but I think I cannot give it.. I did not find an option..
Let me know if the question is not clear enough..
Manoj Deshpande.
I've built a simple custom data flow transformation component following the Hands On Lab (http://www.microsoft.com/downloads/details.aspx?familyid=1C2A7DD2-3EC3-4641-9407-A5A337BEA7D3&displaylang=en) and the Books Online (ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9/html/adc70cc5-f79c-4bb6-8387-f0f2cdfaad11.htm and ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9/html/b694d21f-9919-402d-9192-666c6449b0b7.htm).
All it is supposed to do is create an output column and set its value to the result of calling a web service method (the transformation is synchronous). Everything seems fine, but when I run the data flow task that contains it, it doesn't generate any output. The Visual Studio debugger displays it as yellow, with 1,385 rows going into it, but the data viewer attached to its output is empty. The output metadata looks just like I expect: all of my input columns plus the new column, correctly typed. No validation or run-time warnings or errors are reported.
I'll include the entire C# file below, which only overrrides the ProvideComponentProperties, Validate, PreExecute, ProcessInput, and PostExecute methods of the parent PipelineComponent class.
Since this is effectively a specialization of the DerivedColumn transformation, could I inherit from the class that implements the DC component instead of PipelineComponent? How do I even find out what that class is?
Thanks! Here's the code:
using System;
// using System.Collections.Generic;
// using System.Text;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
namespace CustomComponents
{
[DtsPipelineComponent(DisplayName = "GID", ComponentType = ComponentType.Transform)]
public class GidComponent : PipelineComponent
{
///
/// Column indexes for faster processing.
///
private int[] inputColumnBufferIndex;
private int outputColumnBufferIndex;
///
/// The GID web service.
///
private GID.WS_PDF.PDFProcessService gidService = null;
///
/// Called to initialize/reset the component.
///
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
// Remove any existing metadata:
base.RemoveAllInputsOutputsAndCustomProperties();
// Create the input and the output:
IDTSInput90 input = this.ComponentMetaData.InputCollection.New();
input.Name = "Input";
IDTSOutput90 output = this.ComponentMetaData.OutputCollection.New();
output.Name = "Output";
// The output is synchronous with the input:
output.SynchronousInputID = input.ID;
// Create the GID output column (16-character Unicode string):
IDTSOutputColumn90 outputColumn = output.OutputColumnCollection.New();
outputColumn.Name = "GID";
outputColumn.SetDataTypeProperties(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR, 16, 0, 0, 0);
}
///
/// Only 1 input and 1 output with 1 column is supported.
///
///
public override DTSValidationStatus Validate()
{
bool cancel = false;
DTSValidationStatus status = base.Validate();
if (status == DTSValidationStatus.VS_ISVALID)
{
// The input and output are created above and should be exactly as specified
// (unless someone manually edited the persisted XML):
if (ComponentMetaData.InputCollection.Count != 1)
{
this.ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component accepts 1 Input.",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISCORRUPT;
}
else if (ComponentMetaData.OutputCollection.Count != 1)
{
this.ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component provides 1 Output.",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISCORRUPT;
}
else if (ComponentMetaData.OutputCollection[0].OutputColumnCollection.Count != 1)
{
this.ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component Output must be 1 column.",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISCORRUPT;
}
// And the output column should be a Unicode string:
else if ((ComponentMetaData.OutputCollection[0].OutputColumnCollection[0].DataType != DataType.DT_WSTR) ||
(ComponentMetaData.OutputCollection[0].OutputColumnCollection[0].Length != 16))
{
ComponentMetaData.FireError(0, ComponentMetaData.Name,
"Invalid metadata: component Output column data type must be (DT_WSTR, 16).",
string.Empty, 0, out cancel);
status = DTSValidationStatus.VS_ISBROKEN;
}
}
return status;
}
///
/// Called before executing, to cache the buffer column indexes.
///
public override void PreExecute()
{
base.PreExecute();
// Get the index of each input column in the buffer:
IDTSInput90 input = ComponentMetaData.InputCollection[0];
inputColumnBufferIndex = new int[input.InputColumnCollection.Count];
for (int col = 0; col < input.InputColumnCollection.Count; col++)
{
inputColumnBufferIndex[col] = BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[col].LineageID);
}
// Get the index of the output column in the buffer:
IDTSOutput90 output = ComponentMetaData.OutputCollection[0];
outputColumnBufferIndex = BufferManager.FindColumnByLineageID(input.Buffer, output.OutputColumnCollection[0].LineageID);
// Get the GID web service:
gidService = new GID.WS_PDF.PDFProcessService();
}
///
/// Called to process the buffer:
/// Get a new GID and save it in the output column.
///
///
///
public override void ProcessInput(int inputID, PipelineBuffer buffer)
{
if (! buffer.EndOfRowset)
{
try
{
while (buffer.NextRow())
{
// Set the output column value to a new GID:
buffer.SetString(outputColumnBufferIndex, gidService.getGID());
}
}
catch (System.Exception ex)
{
bool cancel = false;
ComponentMetaData.FireError(0, ComponentMetaData.Name, ex.Message, string.Empty, 0, out cancel);
throw new Exception("Could not process input buffer.");
}
}
}
///
/// Called after executing, to clean up.
///
public override void PostExecute()
{
base.PostExecute();
// Resign from the GID service:
gidService = null;
}
}
}
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)
We have 15 clients running our applicaton
14 of then conected to SQL server using TCP/IP and it runs fine
1 of 15 when connected using TCP/IP produce "..Time out error "
but runs fine when swiched from TCP/IP to Named pipes
1.What area should we look to correct problem with Time out using TCP/IP ?
2. Where to get information about using TCP/IP via Named pipes ?