I am creating a cube with the following tables: Customer CustomerID, Name Customer Rep CustomerID, RepID Rep RepID, Name The important thing here is that there is a many to many relationship between Reps and Customers. I want to be able to ask the question "How much sales for customers working with rep 'A'?" In the data source view...
I have the following T-SQL to display all the permissions granted to principals on my SQL server 2005: select dp.NAME AS principal_name, --1 dp.type_desc AS principal_type_desc, --2 o.NAME AS object_name, --3 p.permission_name, --4 p.state_desc AS permission_state_desc --5 from sys.database_permissions p left OUTER JOIN ...
I'm working on a table design that could involve many NULL values in about 10 fields maybe 75% of the time the fields would be unused. I just generated some fake data (a million records) and could not sense any impact on SQL Server 2005. Size difference was in the KB. Performance - no measurable difference after adding an index to the 3...
I have noticed that a fair number of developers are still using SQL Server 2005. Is there any compelling reason not to use SQL Server 2008? Is it simply that your employer sees no benefit in upgrading? I am planning to start a new project soon, and I am wondering which version would be better to use. ...
How do I Start the MSDTC service on SQL Server 2005? ...
This select statement gives me the arithmetic error message: SELECT CAST(FLOOR((CAST(LeftDate AS DECIMAL(12,5)))) AS DATETIME), LeftDate FROM Table WHERE LeftDate > '2008-12-31' While this one works: SELECT CAST(FLOOR((CAST(LeftDate AS DECIMAL(12,5)))) AS DATETIME), LeftDate FROM Table WHERE LeftDate < '2008-12-31' Could there be...
I've run into a problem related to converting datetimes from XML (ISO8601: yyyy-mm-ddThh:mi:ss.mmm) to SQL Server 2005 datetime. The problem is when converting the milliseconds are wrong. I've tested both implicit and explicit conversion using convert(datetime, MyDate, 126) from nvarchar, and the result is the same: Original ...
We have a very large number of SSIS jobs that are scheduled every evening/early morning to run in succession. These jobs populate and update large amounts of data for our production systems. Recently, we have begun receiving an error message on different jobs at different times. So far, it has been impossible to reproduce on a consistent...
I have a table like this: rowInt Value 2 23 3 45 17 10 9 0 .... The column rowInt values are integer but not in a sequence with same increament. I can use the following sql to list values by rowInt: SELECT * FROM myTable ORDER BY rowInt; This will list values by rowInt. How can get get the difference of Valu...
In SQL Server 2005 I have a maintenance plan set up to back up databases. I have a notify task. I am getting a blank email. The maintenance job history shows the error: Could not generate mail report. An exception occurred while executing a Transact-SQL statement or batch.Supply either @id or @name. I have set up database mail, I have ...
I am trying to write a query to find all USERS in a USERS table that are similar. Its like finding all non distinct rows but using a LIKE statement not an equals statement. Below is an example column of a USERS table USERNAME ------------ tim.smith doug.funny tim.smith1 dan.snyder tim.smith20 doug.funny2 emily.hunt after query the o...
We are trying to come up with an Active - Active DR strategy for our 6 TB data warehouse. Our datawarehouse has 40 DBs and everything has to be replicated on a real time bases. Site 1 : Needs to handle all the ETL Site 2 : Will handle all the reporting queries. Database Mirroring (Cannot afford to drop and create snapshots as we cann...
I am not a big fan of Full Text search with MSSQL(not sure if there is another) does anyone know of any other options to this? ...
I have table with pageId, parentPageId, title columns. Is there a way to return unordered nested list using asp.net, cte, stored procedure, UDF... anything? Table looks like this: PageID ParentId Title 1 null Home 2 null Products 3 null Services 4 2 Category 1 5 ...
Hi everyone, I'm trying to implement a search, where you can enter more than one searchterm to form an AND-Condition. Also it should search in different fields of the database. So for instance: You when you enter "Bill Seattle", you should get a record where NAME matches Bill and CITY matches Seattle. You shoudn't get any rows where ju...
This may have been asked before, but it's really hard to search for terms that limit the search results... Take the following SQL snippet: declare @source table (id int) declare @target table(id int primary key, sourceId int) set nocount on insert into @target values (0,0) insert into @source(id) values(1) --insert into @source(id)...
I have a reporting services project with lots of reports developed in SQL server business intelligence development studio that comes with sql server 2008. I want to deploy these reports to a server that has reporting services 2005 only but the deployment is failing. I'm wondering if any one around here has been able to deploy reports dev...
I came across some vb macros that export it to a mdb ( ms access ) file. But does not work very well when copying forward constraints , etc. I want generate schema from the database diagram for sql server 2005 database. ...
I have merge replication set up between two databases and am using identity ranges on both. I want to add a specific row to a merged table (setting the identity value to something outside of the identity range) on the publisher. When I try this, I get the following error. The insert failed. It conflicted with an identity range check c...
I've created a derived column that translates a 1 to an 'M' and a 2 to 'F'. i.e. a gender indicator. The derived column feeds into a Fuzzy Lookup transformation and then to a conditional split. The problem is the derived field does not show up in any of the downstream components. In the Fuzzy Lookup transform the "Pass Through" check...