I know there are duplicate account numbers in this table, but this query returns no results. SELECT [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1],[ACCT NBR 2], COUNT([ACCT NBR 1]) AS NumOccurences FROM DebitCardData.dbo.['ATM Checking Accts - Active$'] GROUP BY [CARD NUMBER],[CUSTOMER NAME],[ACCT NBR 1],[ACCT NBR 2] ...
I have created a number of type 1 dimensions to hold customer/subscription level details. These dimensions are very large compared to any other dimensions I am using with nearly a 1 to 1 relationship to facts. The dimensions are only being used to provide drillthrough details. It's all working but the size of these dimensions is quite...
A search of SO yields many results describing how to select random rows of data from a database table. My requirement is a bit different, though, in that I'd like to select individual columns from across random rows in the most efficient/random/interesting way possible. To better illustrate: I have a large Customers table, and from th...
I'm trying to better understand why one of our database update scripts failed to work properly at a particular customer site, and narrowed it down (I think) to database ownership and roles. Disclaimer: I'm actually waiting to hear back from the customer's DBA so they can tell us if they upgraded their SQL database recently and so we...
We have developers some developers who are developing against a SQL Server 2005 database, while others are using 2008. We just discovered that generating the edmx against a 2008 database set the ProviderManifestToken to 2008, which means some queries won't work against a 2005 database. While this is a known issue, is there any way to...
I have a schema that includes tables like the following (pseudo schema): TABLE ItemCollection { ItemCollectionId ...etc... } TABLE Item { ItemId, ItemCollectionId, ContributorId } I need to aggregate the number of distinct contributors per ItemCollectionId. This is possible with a query like: SELECT ItemCollectionI...
a) A SQL statement is a single SQL command (for example, SELECT * FROM table1 or SET NOCOUNT ON). A batch on the other hand, is a number of SQL statements sent to the server for execution as a whole unit. The statements in the batch are compiled into a single execution plan. Batches are separated by the GO command So the only diffe...
a) I found two definitions of schema: FIRST - A set of information that describes a table is known as a schema, and schemas are used to describe specific tables within a database, as well as entire databases (and the relationship between tables in them, if any). SECOND - A database schema is a way to logically group objects ...
In SQL Server is there a command to return a list of all tables with a relationship to a given table or view? EDIT: SQL SERVER 2008 ...
I can't get this script to run, because SQL management studio 2008 says the table "NewName" does not exist. However, the script's purpose is to rename an existing database, so that it does exist when it gets to that line. Ideas? Use Master; ALTER DATABASE OldName SET SINGLE_USER WITH NO_WAIT; ALTER DATABASE OldName MODIFY NAME = NewNam...
I have been trying to get SqlCacheDependency working. I think I have everything set up correctly, but when I update the table, the item in the Cache isn't invalidated. Can you look at my code and see if I am missing anything? I enabled the Service Broker for the Sandbox database. I have placed the following code in the Global.asax fil...
I've banging my head for hours, it seems simple enough, but here goes: I'd like to create a view using multiple select statements that outputs a Single record-set Example: CREATE VIEW dbo.TestDB AS SELECT X AS 'First' FROM The_Table WHERE The_Value = 'y' SELECT X AS 'Second' FROM The_Table WHERE The_Value =...
So, it seems that VS 2010 does not support SRSS projects. At least not old ones....Does anyone know what the status on this situation is and whether one can author new SRSS reports using VS2010 or if one should just use VS2008 BIDS? ...
I find many sleeping process my SQL Server database and looks like one of those sleeping SPIDs is blocking another process, and that process is getting suspended too... Could some one please explain this... 1.) How can a sleeping process block another process? 2.) I see many sleeping process...is this normal? Thanks ...
I have a fairly complicated mathematical function that I've been advised should be implemented as a User Defined Function in SQL Server so that it can be used efficiently from within a SQL query. The problem is that it must be very efficient as it may be executed thousands of times per second, and I subsequently heard that UDFs are ve...
Sorry - my question title is probably as inept at my attempt to do this. I have the following (well, similar) in a table in a CMS pageID key value 201 title Page 201's title 201 description This is 201 201 author Dave 301 title Page 301's title 301 descriptio...
Is there any plugings or text editor to auto-complete SQL statement? I am using SQL Server Management Studio (ssms) 90% of time, but most of time I have to type SELECT FROM WITH(NOLOCK) WHERE blahblah. Trying to find a more efficient way. Thanks. ...
I have datetime value as 2010-04-07 09:00:00.000 2010-04-07 14:30:00.000 how to convert the 14:30 to 2:30 pm ...
I am trying to move project which was using MySQL database to the one that uses SQL Server 2008, But the select that was working in mysql is no longer working in SQL Server PreparedStatement statement = connection .prepareStatement("select u.user_firstname,u.user_lastname from user_details u, login l where l.username...
I got to populate FactTable with 12 lookups to dimension table to get SK's, of which 6 are to different Dim Tables and rest 6 are lookup to same DimTable (type II) doing lookup to same natural key. Ex: PrimeObjectID => lookup to DimObject.ObjectID => get ObjectSK and got other columns which does same OtherObjectID1 => lookup to DimOb...