I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables. My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event. My solution to this is to add a CTE that groups scheduled events and counts ...
I have a cube developed using SQL Server Analysis Services (2005). Its hooked up to an excel front end. At the moment users have their own logon to the reporting server and access the reports that way. However, it would be nice of they could access the reports over the web. Are there options for this? I could upgrade to 2008 if there w...
I have 3 tables: CRSTasks (ID,parentID) CRSTaskReceivers (ID,tskID,receiverID) UserNames (id,name) ...relation between CRSTasks and CRSTaskReceivers one-to-many between UserNames and CRSTaskReceivers one-to-one tasks ID parent 1 null 10 1 50 1 taskReceivers id taskID receiverID 1 1 ...
I have these data on a table (using SQL Server 2005): ID ParentID StartTime EndTime 77 62 08:00:00 11:00:00 78 62 12:00:00 15:00:00 79 62 18:00:00 22:00:00 and I want to transform it into this: ParentID BreakfastStart BreakfastEnd LunchStart LunchEnd DinnerStart DinnerEnd...
One third party app is storing data in a huge database (SQL Server 2000/2005). This database has more than 80 tables. How would I come to know that how many tables are affected when application stores a new record in database? Is there something available I can retrieve the list of tables affected? ...
I have used CLR stores procedures in SQL server for awhile now, but I'm still wondering what the best situations to use them are. MSDN provides some guidelines for use like heavy string manipulation (regex), or replacing T-SQL that declares lots of table variables and cursors. I'm curious to know what problems SO users are solving with...
Suppose that I have a source table like this: Source SourceKey (PK) Data1 Data2 Amount And I am loading data from 'Source' into two related tables that look like this: Summary SummaryKey (PK) Data1 Data2 Amount Detail DetailKey (PK) SummaryKey (FK) SourceKey (FK) Data2 Amount EDI...
We're evaluating EF4 and my DBA says we must use the NOLOCK hint in all our SELECT statements. So I'm looking into how to make this happen when using EF4. I've read the different ideas on how to make this happen in EF4, but all seem like a work around and not sanctioned by Microsoft or EF4. What is the "official Microsoft" response to...
Is there any way to display all of the stored procedures in a database? ...
Does the recovery model of a database effect the size of the tempdb.mdf? We have a database which involves a lot of processing and bulk inserts. We are having problems with the tempdb file growing to extremely large sizes (over 70 gb). The database in question is set to Full Recovery. Will changing it to Simple Recovery(on the database ...
Is there a way to truncate tables with constraints ? I tried to DEACTIvATE with this: DECLARE @SQLtxt varchar(max) SET @SQLtxt = '-- DESACTIVER LES CONTRAINTES' + CHAR(10) SELECT @SQLtxt = @SQLtxt + 'ALTER TABLE [' + name + '] NOCHECK CONSTRAINT ALL;' FROM sys.tables PRINT @SQLtxt Of course, it didn't worked. I have to drop the const...
I'm writing a reporting stored procedure. I want to get the number of non-Acknowledged and non-Invoiced Purchase Orders, with the ability to (optionally) filter on CustomerID. What I have below works as expected, but I worry that a) it's slow and b) there's duplication in the CustomerID portion of the WHERE clause. How would you write...
So our application supports both MySQL and SQL Server, and we store images in the database. Now the following code works for MySQL just fine, but not at all for SQL Server. I keep getting an "MDB2: Syntax Error". At first, I had the column as an IMAGE and then I changed it to VARBINARY(MAX) and I still can't figure out what's wrong. Any...
Using SQL Server 2005: I have one stored proc that calls several others within it. One code segment, as an example, goes: INSERT INTO Log (...) VALUES (A...) EXECUTE StoredProcA params... INSERT INTO Log (...) VALUES (A...) INSERT INTO Log (...) VALUES (B...) EXECUTE StoredProcB params... INSERT INTO Log (...) VALUES (B...) It appe...
I am most confused with this one, so i d better ask the experts! These are the rows returned by a custom query of mine. Col1 Col2 Result Font Bold a Font Bold b Font Bold a Font Italic a Is there any way of using selecting count in the above (table) results in order getting this one? Col1 Col2 ResultA ResultB ...
Hello, I want to know which record is the last inserted on Detail Table B in the following order... for example If I have : 1 row Header Table A -------------------- 1 row Detail Table B 2 row Detail Table B 3 row Detail Table B 4 row Detail Table B (last) I want to do some T-SQL or run a procedure if the 4 row is inserted... Thanks i...
I'm looking for an elegant way to select fields from a view, where a second supporting table defines the fields that should be selected. The view contains all of my employee data. The constraint table contains the field name, which references the view field name, and a bit which signifies to select that field. Example of View: Name ...
Hello all, I have a Journal_Entry table, with a Primary Key of Journal_Entry_ID, and (among other columns) an Entry_Date column. I'm trying to do a query that selects the most recent Entry_Date -- via a SELECT MAX(Entry_Date) -- but the problem is that the user may have logged more than one entry on a given date. So if the user logged ...
I'm wondering if anyone out there has run into a realistic max number of table partitions in sql server 2005. I know that the docs state that there is a limit of 1000 partitions per table, but I have a hard time believing that, without a lot of hacking and mucking about, that 1000 partitions would be all that usable. Any help is appreci...
In SSMS, when editing a row of data in a table, you can press Ctrl+0 to enter a null in the current cell. Is there any shortcut for entering the current date in a cell? ...