I have a data warehouse containing typical star schemas, and a whole bunch of code which does stuff like this (obviously a lot bigger, but this is illustrative): SELECT cdim.x ,SUM(fact.y) AS y ,dim.z FROM fact INNER JOIN conformed_dim AS cdim ON cdim.cdim_dim_id = fact.cdim_dim_id INNER JOIN nonconformed_dim AS dim ON d...
How would I go about spawning a separate process using a SQL Server 05/08 trigger? Ideally, I would like to spawn the process and have SQL Server not wait on the process to finish execution. I need to pass a couple parameters from the insert that is triggering the process, but the executable would take care of the rest. ...
This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null value (bad data) which caused that query to return a count of 0 records. I sort of understand why but I could use some help fully g...
I'd like to know what stored procedures are currently running to diagnose some performance problems. How can I find that out? ...
I understand the differences between optimistic and pessimistic locking*. Now could someone explain to me when I would use either one in general? And does the answer to this question change depending on whether or not I'm using a stored procedure to perform the query? *But just to check, optimistic means "don't lock the table while re...
What is the C# equivalent to the sql server 2005 real type? ...
How would one go about profiling a few queries that are being run from an ASP.NET application? There is some software where I work that runs extremely slow because of the database (I think). The tables have indexes but it still drags because it's working with so much data. How can I profile to see where I can make a few minor improvement...
It is a bit of a "chicken or egg" kind of query, but can someone dreamup a query that can return the name of the current database in which the query executes? Believe me when I say I understand the paradox: why do you need to know the name of the database if you're already connected to execute the query? Auditing in a multi-database envi...
Ok I followed the steps for setting up ruby and rails on my Vista machine and I am having a problem connecting to the database. Contents of database.yml development: adapter: sqlserver database: APPS_SETUP Host: WindowsVT06\SQLEXPRESS Username: se Password: paswd Run rake db:migrate from myapp directory ---------- rake abo...
On Sql Server 2000, is there a way to find out the date and time when a stored procedure was last executed? ...
Hi everyone! I'm currently planning the infrastructure for my future web project. I want to go the way Joel went with having one DB per client and now thinking which DB engine will be good for me. The best would be of course SQL Server, but I can't afford a full-blown version at this moment and I don't think SQL Server Express will be a...
I'm wondering if updating statistics has helped you before and how did you know to update them? ...
Running under Server 2008, SQL Server 2005 SP2 has the option to "Add New Administrator" from the SQL Server 2005 Surface Ara Configuration. This launches the SQL Server 2005 User Provisioning Tool for Vista. You are then able to grant privileges such as Server SysAdmin role etc. When I do this the privileges are copied across and appea...
There is a UNIQUE database constraint on an index which doesn't allow more than one record having identical columns. There is a piece of code, managed by Hibernate (v2.1.8), doing two DAO getHibernateTemplate().save( theObject ) calls which results two records entered into the table mentioned above. If this code is executed without ...
I need to add a specific column if it does not exist. I have something like this, but it always returns false.: IF EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can I check if a column exists on a table in SQL Server? ...
In MS SQL 2000 and 2005, given a datetime such as '2008-09-25 12:34:56' what is the most efficient way to get a datetime containing only '2008-09-25'? Duplicated here. ...
I'm trying to design some tables to store some data, which has to be converted to different languages later. Can anybody provide some "best practices" or guidelines for this? Thanks ...
When trying to call Close or Dispose on an SqlDataReader i get a timeout expired exception. If you have a DbConnection to SQL Server, you can reproduce it yourself with: String CRLF = "\r\n"; String sql = "SELECT * " + CRLF + "FROM (" + CRLF + " SELECT (a.Number * 256) + b.Number AS Number" + CRLF + " FROM master..spt_v...
I want to insert say 50,000 records into sql server database 2000 at a time. How to accomplish this? ...
Does anyone have a good set of Visio Stencils for MS SQL Server? ...