Need help with a SQL join: Conditional Matching?
I'm joining 2 tables. ACCOUNTS account_number region product 1234 100 AB 2345 0 AB 3456 300 CD 4567 0 CD PRODUCTS product region price AB 100 $1.50 AB 0 $1.25 ...
sql-server-2005
a field that can be null until it receive something in it?
If you have a field(foreign key) in a table that can accept null until there is something in it, so after that the field would refuse null value. how would you do this? ...
How can I recover Unicode data which displays in SQL Server as ??????
Hi there, I have a database in SQL Server containing a column which needs to contain Unicode data (it contains user's addresses from all over the world e.g. القاهرة for Cairo) This column is an nvarchar column with a collation of database default (Latin1_General_CI_AS), but I've noticed data inserted into it via SQL statements contain...
Changing SQL server compatibility Level
I have a database which was upgraded from 2000 to 2005. Is there any issue using sp_dbcmptlevel to change compatibility level to 90. Will this cause any issues with old queries or stored procedures ...
Omitting the Milliseconds in a Date
Hello, When I select from SQL Server, I want to get a date, but omit the millisecond value, and I want it to be as a date type. So if I have a value 1/1/2009 1:23:11.923, I want to omit the millisecond but retain the date type, so that it will be the value 1/1/2009 1:23:11.000 (I know you really can't omit the millisecond value with a ...
sql query sub select on itself confusion
I'm trying to write a sql statement which returns bad rows from my table. I have a table with rows: Key | Date | Indicator 1 | 10/10/08 | SE 1 | 10/11/09 | CB 1 | 10/12/09 | CE 1 | 10/13/09 | TR 2 | 1/1/09 | SE 3 | 10/10/08 | SE 3 | 10/13/09 | CE 3 | 10/15/09 | SL So what I want returned would be all rows where a key has an in...
How to find what Stored Procedures are using what indexes?
I am trying to determine what indexes are no longer used in my Database. I have had great luck using the following query: SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], I.[NAME] AS [INDEX NAME], i.Type_Desc as [Index Type], USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES F...
Dynamic Columns - SQL Server - Months as Columns
DB: SQL Server 2005 We have a table that has data in this manner: Project Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov ...
How to write stored procedure to do this?
I would like to create a stored procedure that takes in a string of comma separated values like this "1,2,3,4", and break it apart and use those numbers to run a query on a different table. so in the same stored procedure it would do something like select somefield from sometable where somefield = 1 select somefield from sometable wher...
How do I move SQL Server error log files to a new location?
Hi, my default SQL Server 2005 log directory is full on C drive. In order to prevent this issue happening in future, I plan to move the default log directory to some other place. Could you please tell me how I can move the error log default directory? I browsed the web, there is solution for SQL Server 7 and 2000 but not 2005. Please ...
Linking SQL 2005 tables in Access (Client file) and distributing to users
I've used the Access 2007 upsizing wizard to move my files to a SQL server. A problem I'm experiencing now is that when I try to link the tables in either Excel or Access, it requires me to create a DSN or a machine data source connection. In either case, when I do this, it makes the connection only useful on the current PC. What I'...
Are SQL Server Views processed "before" enclosing query logic
If I have a view, and embed the view in a query, will the view have to be processed fully before the rest of the query? Example: CREATE VIEW dbo.ExpensiveView AS SELECT IndexedColumn, NonIndexedColumn FROM dbo.BigHairyTable WHERE NonIndexedColumn BETWEEN 500000000 AND 500050000 GO SELECT * FROM dbo.ExpensiveView WHERE In...
Why does the sys.indexes table have a NULL Index name?
I ran this query: SELECT i.name AS IndexName, s.used_page_count * 8 AS IndexSizeKB FROM sys.dm_db_partition_stats AS s JOIN sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id WHERE s.[object_id] = object_id('dbo.Stu') ORDER BY i.name and the largest index returned ...
SQL - How to insert results of Stored_Proc into a new table without specifying columns of new table?
Using SQL Server 2005, I'd like to run a stored procedure and insert all of the results into a new table. I'd like the new table to have its columns automatically configured based upon the data returned by the stored procedure. I am familiar with using the SELECT ... INTO syntax: SELECT * INTO newtable FROM oldtable Is this possibl...
How to run a sql script using C#
I have a sql script to create a new database which i need to create when our product is installed. For this i need to fire the script using c#. DB is sql-server 2005 express. Plz help.... The sql script is as follows: USE [master] GO /****** Object: Database [Jai] Script Date: 02/12/2010 11:01:25 ******/ CREATE DATABASE [Jai] ON P...
Is there an equivalent to typedef in Transact-SQL?
Is there any way of aliasing datatypes in T-SQL (SQL Server 2005), a bit like a C typedef? I'd like to be able to alias, say, VARCHAR(20) as 'my_datatype' so that wherever I wish to use VARCHAR(20) I could instead use my_datatype. Synonyms allow you to do this sort of thing for tables, and there are built-in synonyms for some datatypes ...
SQL Data Type for primary key in format of a version number
The table I am working on has a primary key which is essentially a version number. A couple of examples of the version number are below: 1.0.0.0 1.1.2.24 Varchar seemed the most obvious choice to me, but I'm looking to set the primary key as a clustered index - I have read on SQL performance sites that you should avoid using varchars ...
Ranking before grouping problem in SQL Server 2005
HI, This should be easy but I don't understand enough about how grouping works. Basically I have 2 tables "Categories" and "Items" Categories ID CategoryName Items ID CategoryID ItemName Photo Score All I want to do is get 1 row for each category which contains the Category ID, the Category Name and the photo that belongs to the h...
Is it enough to install SP3 of SQL Server 2005 alone?
I installed SQL Server 2005, and I want to install service packs, I have service packs 1, 2, 3, but I want to know, Is it different to install just service pack 3? or I should install first SP1, the SP2 and finally SP3? ...
Automated generation of database change scripts
I'm looking for a way to automated the generation of change scripts for our databases. Currently we use Visual Studio 2008 database edition, which has the Schema Compare option, but I don't see a way to automated those actions. What I'd like to do is create an old and a new database during my daily builds and have schema compare gener...