Recently Indexed Messages:-

Reporting Services :: Aggregate Function On Large Value Type

Apr 20, 2015

We are constrained to use a model that stores records in a main table and various attributes of the record in a second table. Something like this: main record table CAR, attribute table CAR_DETAILS with CAR_DETAILS have key value pairs like "color" "blue" and "doors" "4".

For reports we need to flatten the one to many nature of this to get CAR and color and doors by using an aggregate function like: 

car_id, max(car_detail.value) filtered on car_detail.key = "color", max(car_detail.value) filtered on car_detail.key = "doors". 

This works on other tables but the car_detail table (shall we say) appears to store its values as blobs. In any case when we attempt to aggregate we get "The query uses an aggregate function on a large value type expression. Large value type expressions can not be aggregated."

Since we can't change the model, we would need to use another function to change this to a smaller string (or date, these are actually mostly date), but none of the very limited set of functions available seems to work to make this aggregation possible (and there is no "first" or anything else similar). 

The list of functions available in Report Model Queries can be found at [URL] .....

View 2 Replies

SQL 2012 :: Scheduled Query To Export To Daily XLS File

Jun 17, 2015

I'm using SSMS 2012. I have multiple queries that are run daily(I also have a few that are run every Monday) that I want to schedule to run at midnight and export to a new xls file on the network drive to be used the next morning. Is there something that I can add to each query to accomplish this or would I have to set something else up to call the queries?

View 3 Replies

Transact SQL :: Cannot Change Table Structure

Oct 5, 2015

I have a simple query which is taking about 2.5 minutes to execute. What can be done to speed it up ? I cannot change the table structure. even without the group by, and without the float and division, it takes about 2.25 minutes.

Select 'SV00302' as FileSource,
sum(0) as ActualHours,
sum(cast([Estimate_Hours] as float)/100) as EstHours,
max(MODIFDT+Modified_Time) as ModifiedDateTime


View 8 Replies

Transact SQL :: Carriage Return In Email Body Within A Select Statement

Jul 2, 2015

I am trying to insert a carriage return in the select statement after the web link where I had highlighted code in bold. When I insert a record into the table, I receive the email with the message body is in single line.I need the result to look like this in the message body:

ALTER TRIGGER [dbo].[SendNotification]
ON [dbo].[TicketsHashtags]
-- SET NOCOUNT ON added to prevent extra result sets from


View 2 Replies

Analysis :: Using Named Set To Filter On Dimension Attribute

Jun 4, 2015

I am unable to find solution for the problem while writing a Named Set in my cube.

I have a calculated measures which gives me difference in Sales in PERCENTAGE (%).

When I try to filter out those product codes which went a less than 5 %, I get no records.

I have also tried to filter direct values lets say - Products with sales > 100000 which is working fine.

Following is sample of my Named Set

FILTER([X].[Products Code].members, [Measures].[Diff in Sales]<5)

I believe as the values are in percentage, I am facing this issue.

View 21 Replies

SQL Server 2012 :: Cursor Stop Query At Specific Time?

Oct 29, 2015

This store procedure will get some executable queries from the select statement, the cursor will fetch each rows to execute the query and insert the queries into table_3 to mark as 'E'. Until 17:00, this store procedure will stop execute the queries and just get the queries from select statement insert into table_3 to mark as 'C'.

I don't know why the outputs in table_3 are quiet different than I think. This store procedure comes out with two exactly same queries and one marked as C and another marked as E.

'This is a executable query'
FROM table_1
DECLARE @table_2


View 1 Replies

SQL 2012 :: Entity Framework Returning Cached Data

Mar 23, 2014

I have a datagridview bound to a table that is part of an Entity Framework model. A user can edit data in the datagridview and save the changes back to SQL. But, there is a stored procedure that can also change the data, in SQL, not in the datagridview. When I try to "refresh" the datagridview the linq query always returned the older cached data. Here's the code that I have tried using to force EF to pull retrieve new data:

// now refresh the maintenance datagridview data source
using (var context = new spdwEntities())
var maintData =
from o in spdwContext.MR_EquipmentCheck
where o.ProdDate == editDate
orderby o.Caster, o.Strand
select o;
mnt_DGV.DataSource = maintData;

When I debug, I can see that the SQL table has the updated data in it, but when this snippet of code runs, maintData has the old data in it.

View 0 Replies

Integration Services :: DB Server And SSIS Server On Different Machine?

Jul 30, 2015

As per this link [URL] we can execute packages only when we install Integration Services (DTEXEC works only after that).

As per this link [URL] we can execute packages from DB Server if integration services is not present on same machine and packages will execute from server from which they were started (i.e. DB Server via job).

My scenario, I have DB server installed on machine A and SSIS server on machine B. I am creating sql agent job on machine A and pointing to packages stored in file system on SSIS server B. My confusion is since we did not install integration services on machine A (DB Server), how packages execute from here? Will the packages run from server A but use DTEXEC of server B? If I am not wrong SQL Agent Job also uses DTEXEC to execute the package so how does it work without integration services installed? 

View 3 Replies

T-SQL (SS2K8) :: Split String To Show Only Customer

Aug 7, 2014

The data we are getting from our shipping company has the customer name and customer number attached.

so we could have.. declare @String varchar(25) = 'asdf - 10'
but we also have.. declare @String varchar(25) = 'asdf - jeik - 10'

So how do I strip off the " - 10", when the ending number is not the same number of char's (1,11,111,1111, ect)

I need to match this up with our customer table... on Customer Name.

View 9 Replies

T-SQL (SS2K8) :: Isolating A List With Pattern Search

Jul 14, 2014

I'm trying to pull up a report of restored databases from our bug tracking software to audit and see if any of them can be taken down, and having some trouble figuring out how to pull the list of databases out of the entire request text, since they usually come in via email. Some sample data is below with the paltry beginning of a solution that I came up with.

WITH bug (BugID, BugComment) AS (
SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick'
SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick'
SELECT 3, 'Urgent DB_001000 DB_001000 Glick'

[Code] ....

View 6 Replies

SQL Server 2012 :: Incorrect Syntax Near Keyword CASE

Aug 4, 2015

I'm getting an error as "Incorrect syntax near the keyword 'CASE' ". When trying to run this query.

@RiskRef varchar(100)
--drop table #Tmp


View 9 Replies

How To Accumulate A Column

Aug 9, 2014

I would like to accumulate the 'diferencia' column by 'codigo'.

DECLARE @stock_prices TABLE
(codigo char(7)
,fecha date
, precio decimal(10,3)
INSERT INTO @Stock_prices

[Code] ...

View 7 Replies

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL


View 7 Replies

Integration Services :: Configure SMTP Server To Connect To GMAIL In Local System To Use Send Email Task

Sep 15, 2015

I am doing some ssis package development in my local machine. I have a requirement to send email the output.

I am developing in my local machine. Is it possible to configure SMTP configuration in my local system to receive the email in Gmail?

if yes, what all are the steps to be followed.

View 3 Replies

Format For A Date - Calculating Age?

Mar 3, 2015

I am trying to calculate age from a date that is short. I have 5.5 million rows and the format looks like this 1945-01-01. I have my query which is:

select mcrmain.dateofbirth,
day(dateofbirth)as d,
month(dateofbirth) as m,
year(dateofbirth)as y, year('1/1/2015') - year(dateofbirth) as age
from anthemq.dbo.MCRMain;

Is the '1/1/2015' the correct way to format if I want a person's age as of 1/1/2015? The output looks like this:


View 2 Replies

Copyrights 2005-15, All rights reserved