Storing video duration time in sql server.
What's the most appropriate type used to store the duration time information of a video in sql server? ...
sql-server-2005
Schema, Owner for objects in MS SQL
By default, objects (tables, stored procedures, etc) are set up with the dbo owner/schema (I think ms sql 2000 calls it owner, while ms sql 2005 calls it schema) The owner/schema is really a role or user in the database. I've always left the default of dbo, but I've recently seen some examples in microsoft training books where some of ...
Migrating MOSS 2007 from SQL 2000 to SQL 2005
We have an installation of MOSS 2007 that is using MS SQL 2000 as the backend database. The web front end for MOSS is on one server and the database on a separate server. We would like to move the database to a MS SQL 2005 server. Is there a recommended approach for doing this? Does anyone know of any resources that offer tips, trick...
T-SQL: How to get user-defined data-type of the column by it's ID or smth?
I have the query like this SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE FROM MY_DB.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'OrderId' ORDER BY TABLE_NAME GO OrderId column is of user-defined data type. But DATA_TYPE in the query shows underlying system type (i.e. bigint). How to show user-defined type name? ...
Configuring SQL Server 2005 with both server replication and client replication.
I need to set up this scenario: A SQL Server 2005 database will create a transactional replication subscription from another database to populate a set of lookup tables. These lookup tables will then be published as a merge replication publication to the client's SQL Server Mobile. I remember seeing a similar scenario defined in t...
How do I query for foreign keys that don't match their constraints?
SQL Server 2005. I'm adding Foreign Key constraints to the database of an application that allegedly didn't need them. Naturally, the data has become unreliable and there are orphaned entries in the foreign key field. Setup: Two tables, TableUser and TableOrder. TableUser has Primary Key 'UserID', and TableOrder has Foreign Key 'UserI...
Parameterized CREATE VIEW possible?
I've got a (SQL Server 2005) database where I'd like to create views on-the-fly. In my code, I'm building a CREATE VIEW statement, but the only way I can get it to work is by building the entire query string and running it bare. I'd like to use parameters, but this: SqlCommand cmd = new SqlCommand("CREATE VIEW @name AS SELECT @body");...
How to justify more ram for SQL Server 2005
We have SQL Server 2005 running on Win2003/64, and we push it hard. We generate real time reports on several tables with a couple million records using asp.net 3.5, and they will periodically time out. I'd like to increase our memory from 8GB to 16GB or even 32GB to see how that helps, but I need to justify this to the pencil pushers b...
SQL: get DATEDIFF to not return negative values
I have a query in which I am pulling the runtime of an executable. The database contains its start time and its end time. I would like to get the total time for the run. So far I have: SELECT startTime, endTime, cast(datediff(hh,starttime,endtime) as varchar) +':' +cast(datediff(mi,starttime,endtime)-60*datediff(hh,starttime,endtime) as...
How to get partition ranges in SQL 2005
I have a partitioned table in SQL Enterprise 2005. I need to query the PARTITION FUNCTION to find it's partition ranges. What SQL query will give me those values? ...
Accessing another database with dynamic name in SQL Server
There are two databases in SQL Server 2005: One called "A" and another one called "A_2". "A" is a variable name to be entered by the user, the "_2" prefix for the second database is always known. (So databases could be "MyDB" and "MyDB_2", etc) How to access the other database from within a stored procedure without knowing the actual nam...
Insert/Update on SQL Server 2005 without using Stored Procedures
I'm trying to do the classic Insert/Update scenario where I need to update existing rows in a database or insert them if they are not there. I've found a previous question on the subject, but it deals with stored procedures, which I'm not using. I'd like to just use plain SQL SELECT, INSERT and UPDATE statements, unless there's somethi...
What happen in SQL 2005 when it run out of number for an autonumber column?
What happen when SQL Server 2005 happen to reach the maximum for an IDENTITY column? Does it start from the beginning and start refilling the gap? What is the behavior of SQL Server 2005 when it happen? ...
Does SQL Server 2005 have an equivalent to MySql's ENUM data type?
I'm working on a project and I want to store some easily enumerated information in a table. MySql's enum data type does exactly what I want: http://dev.mysql.com/doc/refman/5.0/en/enum.html . Is there an equivalent in SQL Server 2005? I know I could store the possible values in a type table with a key, but I'd rather not have to link ...
Creating a fulltext index on a view in SQL Server 2005
I am having troubles creating a fulltext index on a view in SQL Server 2005. Reviewing the documentation I have not found the problem. The error message I receive is: "'Id' is not a valid index to enforce a full-text search key. A full-text search key must be a unique, non-nullable, single-column index which is not offline, is not defi...
Problems moving an SSIS .dtsx package
I've created a dtsx package with Sql Server Business Intelligence Design studio, and I am executing it using the dtexec utility. Via dtexec I am setting certain properties at runtime using the /set switch. So my command looks something like: dtexec /f "mypackage.dtsx" /set \Package.Connections[Destination].Properties[UserName];myUserNam...
How do I edit a text field (or ntext) in SQL Server 2000 or 2005 using the GUI?
Is there a way to do this with SQL Server tools? If not, is there a 3rd party tool that does this? There doesn't seem to be a good way to edit longer text columns in the SQL Server Managers for SQL Server 2000 or 2005. While SQL Server Manager is really not for editing data in your db, what other tool does Microsoft provide that woul...
Why would LIKE be faster than =?
A co-worker recently ran into a situation where a query to look up security permissions was taking ~15 seconds to run using an = comparison on UserID (which is a UNIQUEIDENTIFIER). Needless to say, the users were less than impressed. Out of frustration, my co-worker changed the = comparison to use a LIKE and the query sped up to under 1...
How to create default instance after creating a named instance?
when installing SQLServer2005 developer edition (not express) I created a named instance: MYSERVERNAME/MYINSTANCENAME Is it possible to 1) change the name of my named instance to default: MYSERVERNAME OR 2) create a new default instance (MYSERVERNAME as above). any help would be appreciated! ...
Altering a trigger in Sql Server 2005
I need to alter a trigger in sql server 2005 and I want to do it using a table adapter and a sql statement, not a stored proc. I know I could run this in the query browser and execute it but I need to deploy it across several databases and use a table adapter in the update. Is this possible? Doing Add Query -> Update -> pasting the belo...