6 Month Gap Doesn't Produce Result

Feb 18, 2008

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

View 19 Replies


ADVERTISEMENT

Service Broker Doesn't Produce What I Expect [Please Read]

Aug 4, 2006

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.

View 7 Replies View Related

Date Query - Produce Unique Line By Month

Oct 21, 2013

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] ....

View 1 Replies View Related

Simple Custom Data Flow Transformation Doesn't Produce Any Output

Aug 30, 2006

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;
}
}
}

View 1 Replies View Related

Combine 2 Queries To Produce One Result Table

Jan 9, 2014

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??

View 3 Replies View Related

SQL Server 2012 :: CTE To Remove Overlaps And Produce Result

Aug 7, 2014

I 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

View 1 Replies View Related

System Stored Procedure Doesn't Return Result (eg: Sp_update_schedule)

Apr 9, 2007

I am trying to catch the @retval which is returned finally after executing sp_update_schedule stored procedure (o or 1) but i cannot catch the final resultIf i put Print statement just before the return (@retval), then i am seeing 0 as output but i want to catch that value when i execute the SP with the parameters. How can i do that?? same thing with all other system stored procedures.thanks alot in advance....if you want more info on this Q, plz let me know 

View 5 Replies View Related

SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists

Jul 27, 2015

I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.

The Similar table would be-

create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)

But in result i want the table as -

Month Value

Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull

View 9 Replies View Related

Parameter Selection Of Month, Showing Selected Month And Sum Up To That Month In Another Row

Apr 5, 2008

Hello what I'd like to display the following in a matrix report:

Parameter selected: 3 (March), 2008 (Year)

Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...

So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.

View 3 Replies View Related

T-SQL (SS2K8) :: Get Last Record In A Month When No Guarantee Month Exists Of Unique Dates?

Apr 22, 2015

following table global_usage

ID varchar (contains alphanumeric values,not unique)
Territory (combined with ID unique)
Total_Used int can be null
Date_ date (date of the import of the data)
ID Territory Total_Used Date_
ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01

[Code] .....

Now the problem,per month I need the most recent value so I'm expecting

ACASC CAL071287 2014-06-01
ACASC CAL071287 2014-08-01
ACASC CAL071288 2014-09-01
ACASC CAL071288 2014-11-01
ACASC CAL071190 2014-12-14
ACASC CAL071286 2015-01-22
ACASC CAL071165 2015-02-01
ACASC CAL071164 2015-03-01

I've tried a few thing like group,having even row_number() but I keep getting wrong results

View 6 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: Convert Number Of Month In Integer Into How Many Year And Month

Sep 10, 2014

This is my table and data

CVID | WorkExperience
--------------------------------
2838736
68181101
96568122
1135484

I need to convert into this result

CVID | WorkExperience
--------------------------------
283873 years
681818 years 5 months
9656812 years 2 months
1135484 months

View 5 Replies View Related

Transact SQL :: Displaying Sales Data In A Month By Month Grid

Aug 11, 2015

Most of the data is in one table. 

Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264

I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.

 SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January', 

View 2 Replies View Related

Produce SQL Statement For A Database

Oct 20, 2004

hi
is there any feature in MSSql that produce SQL statement for a database(include CREATE TABLE, INSERT Records, ...)

View 2 Replies View Related

Produce All Combinations For A Situation

Sep 6, 2013

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...

View 1 Replies View Related

Will OSQL Produce SQL Backup?

Jul 23, 2005

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 Related

Dividing Integers To Produce A Percentage

Aug 27, 2003

I'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.

View 2 Replies View Related

How To Produce A Summary Row For Select With No Results

May 28, 2013

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.

View 3 Replies View Related

Query To Produce A Report In Apex

Jan 24, 2014

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?

View 3 Replies View Related

Produce Data -&&> Store In Table?

Sep 4, 2007

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!

View 3 Replies View Related

Possible To Link 2 Stored Procedures To Produce Only 1 Recordset?

Sep 16, 2004

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

View 14 Replies View Related

How To JOIN Tables To Produce Contiguous Data

Aug 6, 2015

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.

View 2 Replies View Related

T-SQL (SS2K8) :: Join Query Produce Duplicates?

Aug 12, 2014

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]....

View 4 Replies View Related

Can SQLServer Produce Excel Spreadsheet Output ?

Jul 23, 2005

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 Related

Client EM Produce OS Failed Login Errors

Jul 23, 2005

hi 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 Related

Differential Backups Produce 55GB Files

Jul 16, 2007

We 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

View 8 Replies View Related

Can I Attach A .NET Assembly Into SQL 2000 Server As An Extended Produce?

Aug 27, 2004

and how can it be done?

View 1 Replies View Related

SQL Server 2012 :: Query To Produce Permutation Combination

Jun 4, 2015

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),

View 3 Replies View Related

SQL Server 2014 :: Similar Queries Produce Different Output?

Sep 22, 2015

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

View 3 Replies View Related

Transact SQL :: SELECT Query To Produce UPDATE Command

Nov 18, 2015

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

View 9 Replies View Related

Generating Dependencies But Will NOT Produce Any Output In Both Results And Messages Tabs

Mar 16, 2015

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 */

View 2 Replies View Related

Need To Produce A Query That Show The Cities And The Number Of People Per City?

Jan 31, 2008



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?

View 3 Replies View Related

Sproc To Determine If Query Will Produce Invalid SqlNotificationEventArgs Event

Feb 7, 2007

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?

View 1 Replies View Related







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