I have the following table lookup table in OLTP CREATE TABLE TransactionState ( TransactionStateId INT IDENTITY (1, 1) NOT NULL, TransactionStateName VarChar (100) ) When this comes into my OLAP, I change the structure as follows: CREATE TABLE TransactionState ( TransactionStateId INT NOT NULL, /* not an IDENTITY column i...
I have an existing SQL 2005 stored procedure that for some reason, outputs its results in the Messages pane in SSMS instead of the Results pane. (Its actually a CLR procedure already compiled and deployed to all our servers, and used for another daily process. So I can't change it, I just want to use its output.) For the sake of discuss...
I am looking into using the datetimeoffset for SQL Server 2008. According to the MSDN article there is a time zone offset range of -14:00 through +14:00. I can understand going 12 hours from UTC in each direction, one hour per timezone. I think I understand 13 hours in each direction, for daylight savings time. I just dont get 14 hours? ...
I have a fairly large SQL Server database that is using SIMPLE recovery mode. We don't really have a need for up to the second recovery so i'd prefer we stay with this mode. For some reason the transaction log for this database is massive (410 GB) with 99% of the space unallocated. I've tried to shrink the file using ( DBCC SHRINKFILE ...
As I have seen, SQL 2008 no longer offers replication over internet in very simple method. Not only that, I can see that the merge replication method installs too many triggers and views onto database and adds additional field named "rowguid". We have more disc space available today then the processor speed. So I decided to write my own...
I want to create 3D reports which have a good look and feel. I am using VS 2008 and SQL server 2008. Is there any way of creating 3D or 4D reports in SQL server 2008 or can I use the crystal reports package that is bundled with .NET? ...
Scenario: Production servers are running SQL Server 2008 in the domain myDomain.com Dev/test/stage servers are running SQL Server 2008 in the domain dev-myDomain.com Actual dev work done on local running SQL Server 2008 source controlled using SourceSafe First of all, does this setup/environment make sense? And, what are some good wa...
I have a field called PropertyValue in the UserProfile table that can contain information for address, phone number, first name, last name, user name, city, etc... each record in this table is associated to a user by UserId, it is also associated to a ProfilePropertyDefinition which contains the definition for each of the properties (ie....
I have an asp.net web site which will send several ExecuteNonQuery() statements (stored proc) to the same database (Sql server 2005). MSDTC service is turned off on both web and database servers. These statements need to run all successfully, all or none. So I need to wrap them in transaction. Is the best way to use SQLTranscation? Atta...
I have a table T with a primary key id and foreign key f. Is f automatically indexed when it is specified as a foreign key? Do I need explicitly add an index for f ? ...
I've got a new client, blah blah blah, I need to break SQL Server 2000 replication from their home office to another database that they are going to completely drop (at some hosting place, it is no longer needed). The one at the home office is the "Publisher", I believe. Honestly, I think we could just delete the other database and the...
When I generate scripts in sql server 2008 EM, it seems to insert the date/time in the script also. How can I remove that? ...
I'm getting this error Linq to Sql Error - "String must be exactly one character long" Is it possible to put a constraint on a nullable varchar(1) field to allow null but not allow an empty string? ...
I have a continous time dataset and I want to break it up into 15 minute blocks using sql. I don't want to have to create a new table to be able to do this if I can help it. i.e. Time, Count 09:15, 1 09:30, 3 09:45, 0 10:00, 2 10:15, 3 ..... Does anyone have any idea of how I can do this. I presume that is use a select similar ...
I am using VS 2008, SQL Server 2008, IIS 6.0 Manager on an XP OS. I developed my very first C# project in VS 2008 and it runs correctly in VS. But now I need to publish this project on my website. This project also involves an SQL Server 2008 Adventureworks database on this same computer. I will use this same computer to host the webs...
We have a need to update several tables that have parent/child relationships based on an Identity primary-key in the parent table, which is referred to by one or more child tables as a foreign key. Due to the high volume of data, we would like to build these tables in memory, then use SqlBulkCopy from C# to update the database en mass ...
I am modifying a Type 2 dimension using the following (long) SQL statement: INSERT INTO AtlasDataWarehouseReports.District ( Col01, Col02, Col03, Col04, Col05, Col06, Col07, Col08, Col09, Col10, StartDateTime, EndDateTime ) SELECT Col01, Col02, Col03, Col04, Col05, ...
There are 5 different types of logging in SSIS Event Log Text File XML File SQL Server SQL Server Profiler I am in a production environment where developers do not have access to production systems. Which logging method should be my poison of choice, and why? ...
I have a table with an integer column with some fairly large numbers in it. Im trying to average some values in this and sometimes it works other times it gives this error "Arithmetic overflow error converting expression to data type int." I've broken it down and this sample produces the error create table LargeNumbers (number int) i...
In MySQL, if you specify ON DUPLICATE KEY UPDATE and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have identical effect: INSERT INTO table (a,b,c) VALUES ...