AdHoc Reporting Against SQL 2K W/meta Data?
Feb 6, 2004
Okay... We have a SQL2K database that has about 500 tables or so. It is normalized to a reasonable level and enforces all relationships with PK/FKs, not triggers. Hence, for a database-minded person it is fairly easy to read (as easy as a 500+ table database can be!).
Our users need adhoc query capabilities. Our report writer is simply overwhelmed. He doesn't need to be spending time writing a report that is intended to be run once.
I expect the best alternative would be to use some sort of adhoc reporting tool that is based off meta data. We (the DBAs) could be responsible for maintaining the meta data and STILL have a manhour savings over developing all these reports.
Here's the catch... We are on a TIGHT budget (aerospace industry is still reeling a bit). Is anyone using a product or aware of a product that might be just the ticket for us? We have been investigating a product by LogiXML called LGX AdHoc (http://www.logixml.com/products/AdHoc/adhoc.htm). Looks promising. Anyone use or familar with it?
View 1 Replies
ADVERTISEMENT
Sep 28, 2007
Hi,
I am looking for a strategy document for Adhoc reporting. Does MS has published anything in this regard?
Thanks,
S Suresh
View 3 Replies
View Related
May 6, 2008
Hi,
Using Reporting Service 2005, I want to give User an Interface where user can directly paste sql query like -
select * from employee.
and corrosponding data will be displayed to user.
Is there are any way?
Thanks,
Ashwin
View 6 Replies
View Related
Mar 3, 2008
Hi Gurus,
We are planning to user SQL server 2005 reporting services in our project, for this we are doing a Proof of concept exercise to evaluate if SSRS 2005 will be good for our project.
For this we are trying if we can make dashboard or composite reports (combine 4-5 reports to create one report).
Also if we can make this dashboard ad-hoc. As we are already using the Ad-Hoc reporting capability of SSRS 2005 we wanted to check if dashboards can also be created using Ad-hoc reporting feature.
Please let me know if you any information or pointers for this.
Thanks in Advance.
Regards,
-Gaurav
View 1 Replies
View Related
May 27, 2015
I am looking for a solution for my customers who use the 2005 report models to create detail list reports. Â All I am able to find on 2012 are pivot type reports, SSAS tabular and cubes. Â It seems that MS assumes that all clients need statistical or summary reports. Â My users need to be able to create adhoc lists of data from multiple view and tables using drag and drop. Any third party distributable tool which could be used for adhoc reporting?
View 2 Replies
View Related
May 7, 2012
I have 10 oracle o/p tables. I have to transfer data in monthly or adhoc basis. Each table will have millions of records. How to transfer Oracle to SQL Server 2005. Which is the best way to transfer the data.
View 2 Replies
View Related
Apr 16, 2008
Hi all, i got this error:
[DTS.Pipeline] Error: "component "Excel Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
and also this:
[Excel Source [1]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "Fiscal Week" needs to be updated in the external metadata column collection. The column "Fiscal Year" needs to be updated in the external metadata column collection. The column "1st level" needs to be added to the external metadata column collection. The column "2nd level" needs to be added to the external metadata column collection. The column "3rd level" needs to be added to the external metadata column collection. The "external metadata column "1st Level" (16745)" needs to be removed from the external metadata column collection. The "external metadata column "3rd Level" (16609)" needs to be removed from the external metadata column collection. The "external metadata column "2nd Level" (16272)" needs to be removed from the external metadata column collection.
I tried going data flow->excel connection->advanced editor for excel source-> input and output properties and tried to refresh the columns affected.
It seems that somehow the 3 columns are not read in from the source file?
ans alslo fiscal year, fiscal week is not set up up properly in my data destination?
anyone faced such errors before?
Thanks
View 13 Replies
View Related
Jun 28, 2003
I am trying to get a grasp of Meta Data. Why is this important to application development?
View 7 Replies
View Related
May 7, 2007
how can we extract metadata for a database tables.
do we have any sql's for that??
View 2 Replies
View Related
Aug 1, 2007
Hi Guys,
I have a DataBase in which I have several Tables.
What I want is an SP or Query which takes as its parameter the "tablename".
The Output Should be a having three fields only.
Field name, DataType Of the Field, Length of the DataType.
For Example
Suppose the StoredProcedure Name is "SP_GetTables"
if i have a table named "tbl_Users" with fields
UserName varchar(50)
UserPass varchar(20)
UserAge int
UserStatus bit
In my program side if I pass the parameter as "tbl_Users" to the StoredProcedure SP_Users,
I should get the O/P as
Field Name DataType Length
UserName varchar 50
UserPass varchar 20
UserAge int
UserStatus bit
Regards,
Naveen.
View 3 Replies
View Related
Jul 23, 2005
Hi,I would like to prepare a data dictionary for my database (northwind).I have framed the below SQLSELECT'NAME ' = a.name,'DESCRIPTION' = b.value,'Type ' = type_name(a.xusertype),' ' AS 'Values','NULL ' = case when a.isnullable = 0 then ' ' else 'X' end,' ' AS 'PK',' ' AS 'FK'FROMsyscolumns a,sysproperties bWHEREa.id = 2073058421 AND --- Customers Tablea.number = 0 ANDb.id = a.id ANDb.smallid = a.colidORDER BY a.colidand the output would be:NAME DESCRIPTION Type Values NULL PK FK-------------- --------------- ---------- ------ ----- ---- ----CustomerID Customer ID ncharCompanyName Company Name nvarcharContactName Contact Name nvarchar XContactTitle Contact Title nvarchar XAddress Address nvarchar XCity City nvarchar XRegion Region nvarchar XPostalCode Postal Code nvarchar XCountry Country nvarchar XPhone Phone # nvarchar XFax Fax # nvarchar XPK and FK is where I need to print whether the column is Primary Key orForeign Key.If CustomerId is defined as Primary Key then PK should have X printed.Thats the objective.How will I accomplish this ?Thanks in advance,Anu
View 3 Replies
View Related
Aug 6, 2005
I am wanting to set up some kind of metadata catalogue to managemetadata records of the data we collect and create for my companiesclients.I am thinking I want to do this using XML and SQL Server and have sometype of web-based browser to search for records.Any suggestions on existing applications or resources that I could use?Thanks
View 1 Replies
View Related
Jul 20, 2005
Hi,Apologies if this is better posted in an ASP group, but here goesanyway ...Is it possible to work out what parameters a stored procedure expects,using ASP?I would like to take the name of a stored procedure, work out whatinput parameters it has and build a form based on them in ASP.Thanks,MB.
View 2 Replies
View Related
Oct 25, 2006
My customer has a .NET application that reads meta data from SQL Server, Oracle, DB2, and several propritary databases. Because each DBMS stores the meta data using various techniques, they have written custom code for each DBMS. They are working on a generic ODBC/OLEDB suppport, but in the interim I was trying to use SQL Server to link to an Access database. The Access linked server works fine for queries in Query Analyzer, but I would like to be able to programatically read the metadata for an Access DB (tables, columns, types, etc) via the linked server. SQL Server's usual mechanism for storing meta-data in the Master database aparently is not used for Linked Servers.
Does SQL Server expose Linked Server meta data?
How would one retrieve this meta data if it is exposed?
Thanks,
Ben
View 3 Replies
View Related
Apr 18, 2005
Hi
I am running a couple of sql 2000 SP3a servers with merge and snapshot replication. One server acting as publisher and distributor and the rest subscribers. On one of the server I have got the error below and have tried most of the suggestions by msdn. This server has not crashed ever before or any hardware problems. It has been running for a couple of months and no problems. This has not happened no any of the other servers. Any suggestions would be greatly appreciated as the only resolution I have left is to bring up a new instance, setup replication and see if this would resolve the issue. Stopping and starting of agents don't work.
[4/18/2005 11:59:20 AM]BRAAMPDC1ICAS2000.HO_Master: {call sp_MSgetversion }
[4/18/2005 11:59:20 AM]BRAAMPDC1ICAS2000.distribution: {call sp_MShelp_subscriber_info (N'BRAAMPDC1ICAS2000', N'EASTSRV3')}
Connecting to Subscriber 'EASTSRV3.ICASData'
Server: EASTSRV3
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288
[4/18/2005 11:59:27 AM]EASTSRV3.ICASData: {call sp_MSgetversion }
Percent Complete: 2
Connecting to Subscriber 'EASTSRV3'
Percent Complete: 3
Retrieving publication information
Percent Complete: 4
Retrieving subscription information
Percent Complete: 4
The merge process is cleaning up meta data in database 'HO_Master'.
Percent Complete: 4
The merge process cleaned up 0 row(s) in MSmerge_genhistory, 0 row(s) in MSmerge_contents, and 0 row(s) in MSmerge_tombstone.
Percent Complete: 4
The merge process is cleaning up meta data in database 'ICASData'.
The merge process could not perform retention-based meta data cleanup in database 'ICASData'.
Percent Complete: 0
The merge process could not perform retention-based meta data cleanup in database 'ICASData'.
Percent Complete: 0
Category:NULL
Source: Merge Replication Provider
Number: -2147199467
Message: The merge process could not perform retention-based meta data cleanup in database 'ICASData'.
Percent Complete: 0
Category:COMMAND
Source: Failed Command
Number: 0
Message: {call sp_mergemetadataretentioncleanup(?, ?, ?)}
Percent Complete: 0
Category:SQLSERVER
Source: EASTSRV3
Number: 11
Message: General network error. Check your network documentation.
View 1 Replies
View Related
Oct 20, 2006
We are running SQL Server 2000, SP4. I recently noticed that my DTS packages were missing from the local server. Since I had saved them as Structure Storage Files, I imported them back into DTS Local Packages. That was yesterday. Today I opened the Meta Data browser to see what was in it. (We don't use Meta Data Services.) It displayed a message:"An error occurred while trying to access the database information. The msdb database could not be opened." These (restore of DTS pkgs and Meta Data error) may not be related, but I need to know why I am getting this error, because I am about to upgrade to SQL Server 2005 for Workgroups.
1. Does anyone know what causes this error message?
2. Are local DTS packages stored in the MSDB database?
View 1 Replies
View Related
Apr 27, 2004
I am running SQL Server 2000 SP3 and I am trying to save a DTS package into Meta Data Services and I am receiving the following "Package Error"
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: General Error -2147217355 (80041035).
I have searched for this error and I cannot find anything related to it. Also, I saw some of the comments about right clicking "Data Transformation Services" and checking the box for allow save to Meta Data Services, however, I do not see that checkbox to allow for this. Has anyone else had this problem and resolved it? I'm beginning to get very frustrated with it.
View 1 Replies
View Related
Dec 5, 2006
Would someone help me how to Move existing Meta Data Services packages to SQL Server storage (in the msdb database) or to structured storage files before you upgrade from SQL Server 2000 to SQL Server 2005? This is a before action recommendation by Upgrade Advisor.
View 1 Replies
View Related
Sep 1, 2015
I have been given a request by a business analyst to update the text 'old' to 'new' within the column names / measure names and associated calculations within a PowerPivot model. There are hundred of columns / measures / calculations, etc. which need to be renamed.
Is there any way of updating these changes to the model other than making these changes manually or is there some way of doing the following type of operation in PowerPivot; -
UPDATE tblColumnNames SET Column_name, etc REPLACE ('old','new', all columns),('old','new', all measures),('old','new', all calculations)
FROM
tblColumnNames
View 2 Replies
View Related
Jan 29, 2003
I installed sp3 on my sql2000 server and now when I open up enterprise manager I get the following:
1st message:
rebbrui.rll is missing
2nd message:
snap in failed to initialize
name: meta data services
Thanks in advance for your help!!!
View 1 Replies
View Related
May 26, 2006
Hello anyone / everyone,
If you are having trouble accessing Meta Data Services on W2k3 and SQL 2000 and get the error that the "msdb database could not be opened" I found that hot fix 912812 on the operating system is the culprit. Remove it and Meta Data goes back to working, although I am being asked to approve the ActiveX control every time the page refreshes.
Hope this helps somebody, to took me long enough to track it down.
If anyone has a resolution to the ActiveX question I'd love to know the answer.
Thank you,
Uncle Davy
View 5 Replies
View Related
Jan 24, 2007
Hi all,
When I get this message "The merge process is cleaning up meta data in database 'xDatabase'." at distributor MS SQL 2005 replicator monitor on one of his suscription, this proccess generate so proccess charge to the suscriber that users note difference in performance. I wonder if exist a way to change some kind of parameter for this proccess run on specific schedule ?
Any help would be appreciated.
other info I had suscribers that only replicate in one way, maybe this metadata is causing this overcharge.
View 5 Replies
View Related
Sep 8, 2006
Hi SQL Replication Gurus:
I got some issues in my production environment, so please help me out. The following is the message I got from the replication monitor and I don't what to at this point.
Appreciate you help.
Yong
==========================================================================================
Command attempted:
{call sp_mergemetadataretentioncleanup(?, ?, ?)}
Error messages:
The merge process could not perform retention-based meta data cleanup in database 'TT'. (Source: Merge Replication Provider, Error number: -2147199467)
Get help: http://help/-2147199467
Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: ply-db-svr1, Error number: 1205)
Get help: http://help/1205
View 8 Replies
View Related
Dec 28, 2007
hi all;
1. Excel file Source--> monthly Revenue details
2. Derived Colum Transoformations
3. Oledb Destination
its my flow in one of my packates (ETL job)
Excel file contains monthly revenue details, i wanna import the excel data to my database staging table, so i've created the package.
its working fine...
Problem
if we change the new data for the next month and running the package its not running;
the same file, same format, only we delete the contents, of the file except first row of the excel sheet,
and pasting the new data;
new data is coming from Oracle DataBase in the form of excel sheet ( manually they will copy the data and sending to us)
i open that package in design mode and while double clicking the excel file source it says <column name>'s Meta Data needs to be synchronized
Do you want to Fix this issue automatically with the available external column's meta data
Clearly noted that its a data type issue; i have changed the corresponding data types as it is in the previous Excel sheet which is equivalant to the Table its copying to.
now the package is running with validation warnings, External Column "Invoice Amount" needs to be updated...etc. some 2 or three warning messages i can able to see in the package Execution wizard,
ok, i'm ready to accept these warnings, and i want my package running from my server;( packages had been deployed in to the Centeralized server; every time if we want to run the package, we have the asp.net webpage, that is executing the package in an On_click event)
The package is not running from the server, its due to the meta data change in the Excel file( i guess)
please suggest me some guide lines to resolve this meta data issue, i want my excel sheet meta data should not change when we have new updates in it;
otherwise suggest me some solutions that i can validate the excel sheet before running the package and testing whether the data is in correct format or not? its a kind of Data Profiling activity;
i know its some what crazy, but i need to maintain the system with permanent solution, instead of facing this meta data mismatch issue!!!
some what lenthy explanation--> its needed for my dear powerful microsoft responders. i think i 've explained my problem clearly, if i don't let me know your queries, i'll try my level best.
View 3 Replies
View Related
Oct 13, 2006
Here's another one of my bitchfest about stuff which annoy the *** out of me in SSIS (and no such problems in DTS):
Do you ever wonder how easy it was to set up text file to db transform in DTS - I had no problems at all. In SSIS - 1 spent half a day trying to figure out how to get proper column data types for text file - OF Course MS was brilliant enough to add "Suggest Types" feature to text file connection manager - BUT guess what - it sample ONLY 1000 rows - so I tried to change that number to 50000 and clicked ok - BUT ms changed it to 1000 without me noticing it - SO NO WONDER later on some of datatypes did not match. And boy what a fun it is to change the source columns after you have created a few transforms.
This s**hit just breaks... So a word about Derived Columns - pretty useful feature heh? ITs not f***ing useful if it DELETES SOME of the Code itself after there have been changes in dataflow. I cant say how pissed off im about that SSIS went ahead and deleted columns from flow & messed up derived columns just because the lineageIDs dont match.
Meta-data - it would be useful if you could change it and refresh it - im just sick and tired of it that it shows warnings and errors when there's nothing wrong - so after a change i need to doubleclick all my transforms so that those red & yellow boxes would disappear.
Oh and y I passionately dislike Derived columns - so you create new fields based on some data - you do some stuff - combine multiple columns to one, but you have no way saying remove the columns from the pipeline. Y you need it - well if you have 50K + rows with 30+ columns then its EXTRA useless memory overhead for your package.
Hopefully one day I will understand how SSIS works (not an ez task I say) - I might be able to spend more time on development and less time on my bitchfest - UNTIL then --> Another Day - Another Hassle with SSIS
View 5 Replies
View Related
Jan 4, 2007
Hi Guru,When I ran my adhoc script below it generated only 45000 reads or 4seconds but when I wrapped it into procedure it took about two minutesor millions of reads. The parameters calling both adhoc and proc areindeed the same. I'm pretty 99.9% sure that the proc does not recompilebecause I don't mix up between DDL and DML, no temp tables or any thingto cause proc to recompile. The big difference is adhoc used index scanfor 45% but proc used bookmark lookup for 75%. Why it's so differencesince they both returned the same results?Please help...Silaphet,Below is my code,DECLARE @Modevarchar(10),@UserIDvarchar(36),@FromDatesmalldatetime,@ToDatesmalldatetime,@Insttinyint,@LocationIDsmallint,@BunitIDtinyint,@TeamIDintSET @Mode='TEAM'SET @UserID=''SET @FromDate='Dec 1 2006 12:00AM'SET @ToDate='Dec 31 2006 12:00AM'SET @Inst=28SET @LocationID=0SET @BunitID=2SET @TeamID=805--IF @Mode = 'TEAM'BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active,TeamName, '' As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_Grouped RIGHT OUTER JOINdbo.MyTeamsRpt ONvw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerIdLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (MyTeamID = @TeamID) AND ((StartDateBETWEEN @FromDate-365 AND @ToDate-365) OR (StartDate BETWEEN @FromDateAND @ToDate))GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,ActiveENDIF @Mode = 'RM'BEGINIF @BUnitId 0BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''As TeamName, OffBUnitDesc As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_GroupedLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @UserID)AND ((StartDate BETWEEN @FromDate-365 AND @ToDate-365) OR (StartDateBETWEEN @FromDate AND @ToDate)) AND OffBUnitID = @BUnitIDGROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,OfficerName, ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,OfficerName, ActiveEND--ELSEIF @BUnitId = 0BEGINSELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''As TeamName, '' As BUnit,Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1 ELSE 0END) As CurrYr,Sum(CASE WHEN StartDate BETWEEN @FromDate-365 AND @ToDate-365 THEN 1ELSE 0 END) As PrevYr,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND(DATEDIFF(dd,StartDate,@ToDate) * -1 <-30) THEN 1 ELSE 0 END) AsPastDue,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 1037) THEN 1 ELSE 0 END) As Ref,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 63) THEN 1 ELSE 0 END) As CallSched,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 64) THEN 1 ELSE 0 END) As PropPres,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 65) THEN 1 ELSE 0 END) As PropAcc,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 66) THEN 1 ELSE 0 END) As BremApp,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND (StageId= 67) THEN 1 ELSE 0 END) As BusBook,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,Sum(CASE WHEN (StartDate BETWEEN @FromDate AND @ToDate) AND(OutcomeID = 107) THEN 1 ELSE 0 END) As OutdatedFROM vw_Referrals_GroupedLEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =dbo.vw_Officers.OfficerIDWHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @UserID)AND ((StartDate BETWEEN @FromDate-365 AND @ToDate-365) OR (StartDateBETWEEN @FromDate AND @ToDate))GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, ActiveHAVING Sum(CASE WHEN StartDate BETWEEN @FromDate AND @ToDate THEN 1ELSE 0 END)>0 Or Active = 1ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, ActiveENDENDEND
View 3 Replies
View Related
Sep 4, 2007
Hello
We got third party software, we are using adhoc queries more for the Applications (i am checking the DB Dashboard, it is showing as 97% CPU for adhoc queries). Could any one suggest how to tune SQL Server (at server level) for adhoc queries, we can not change the code now.
Let me know how to improve the SQL Server, whenever 3rd party applications use adhoc queries more?
Thanks in advance.
View 1 Replies
View Related
Feb 14, 2008
Working on partitioning a few large tables. One of the tables included a text column and the €œTEXTIMAGE_ON [PRIMARY]€? clause which would prevent the partitioning of this table. After some research we found that the data was legacy and no longer used. We updated the column on the affected rows to NULLS and altered the column to a VARCHAR(20)
I then attempted to run the ALTER TABLE SWITCH and I encountered the error
Msg 4947, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'LocalDeltanet.dbo.testresultsjoe' for the index 'PKIDX_testSummary' in target table 'LocalDeltanet.dbo.testresults_part'.
After a lot of grief and testing I determined that the message was bogus and the real issue is that the 'sys.tables' still has €œlob_data_space_id€? with a value of 1 for this table.
I created a copy of the table with the text column and the "TEXTIMAGE_ON", then altered the column to a varchar and another table with just the varchar column and no "TEXTIMAGE_ON" spoecified. After copying the data from the original table, I tried to run the Alter Switch. It failed once again for the table with the text column that was altered to varchar, but it worked for the table that had the column specified as varchar from the start.
All other things have been checked and the two source tables in this test are identical execpt for the Text column specification. The alter column changes the definition of the column, but how would you remove the €œlob_data_space_id€? setting, since it appears that this value is causing my issues, is there anyway to update the table in place. I know I can BCP the data out, but that would take too long and would defeat the advantage of using the alter switch method.
BOL States:
The allow updates option is still present in the sp_configure stored procedure, although its functionality is unavailable in Microsoft SQL Server 2005 (the setting has no effect). In SQL Server 2005, direct updates to the system tables are not supported. This means we cannot update the table manually.
View 1 Replies
View Related
Oct 31, 2005
Hi !!We are developing an application where we need a Query tool which allows customer to do Ad-Hoc or random query.. something similar to lets say http://salebyowner.com/advancedSearch.phpWe have few more options than this on which customer can do search. I don't think dynamic query in C# code or something like that is going to help me. Am I right? Do we have to use any type of query tool or something for doing this?
View 6 Replies
View Related
Apr 17, 2015
I am trying to write a query that counts how many clients were part of program at the first of each month.To make it simple, the data comes out of one table which looks like this:
Client # Program Start_date End_date
1 Fruit eater 03-27-2014 01-10-2015
2 Veggi eater 01-16-2015 null
3 Veggi eater 12-05-2013 04-16-2015
4 Fruit eater 10-01-2014 11-30-2014
Currently I have a very ugly solution that I know is not the best one:
Select
sum(convert(int,Jan_2014))as Tiers_in_Jan_2014
, sum(convert(int,Feb_2014)) as Tiers_in_Feb_2014
, sum(convert(int,Mar_2014 )) as Tiers_in_Mar_2014
, sum(convert(int,Apr_2014 ))as Tiers_in_Apr_2014
[code]....
Is there a better way to write this query?
View 3 Replies
View Related
Mar 13, 2008
Hi,
i want to know whether it is possible to develop ADHOC reports using SSRS 2005 and OLAP Cubes. the requirement is the user should be able to select the columns he wants to see in the report.
I think this requires Dynamic RDL generation. It is very urgent. i have to give POC to the client on this. Any help in this regad is appreciated.
Thanks,
Srik
View 4 Replies
View Related
Aug 28, 2004
Yesterday i face a strange SQL Server 2000 behaviour :-(
I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.
So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.
Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.
My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?
View 5 Replies
View Related
