In SQLSERVER 2005, I'm using table-valued function as a convenient way to perform arbitrary aggregation on subset data from large table (passing date range or such parameters). I'm using theses inside larger queries as joined computations and I'm wondering if the query plan optimizer work well with them in every condition or if I'm bett...
I have a very simple table and I can happily query it using LINQ To SQL, but when I do any save/updates the GetChangeSet method on my datacontext is always empty. My code is very simple (concatenated code from various classes): public static EntitiesDataContext EntitiesContext { get { return new EntitiesDataContext("Dat...
I have a rather large (many gigabytes) table of data in SQL Server that I wish to move to a table in another database on the same server. The tables are the same layout. What would be the most effecient way of going about doing this? This is a one off operation so no automation is required. Many thanks. ...
I've got a maintenance plan that executes weekly in the off hours. It's always reporting success, but the old backups don't get deleted. I don't want the drive filling up. DB Server info: SQL Server Standard Edition 9.00.3042.00 There is a "Maintenance Cleanup Task" set to "Search folder and delete files based on an extension" and...
How would you handle cross database queries in different environments. For example, db1-development and db2-development, db1-production and db2-production. If I want to do a cross-database query in development from db2 to db1 I could use the fully qualified name, [db1-development].[schema].[table]. But how do I maintain the queries a...
I have seen a couple of questions related to string concatenation in SQL. I wonder how would you approach the opposite problem: splitting coma delimited string into rows of data: Lets say I have tables: userTypedTags(userID,commaSeparatedTags) 'one entry per user tags(tagID,name) And want to insert data into table userTag(userID,tag...
Hi ! In Sql Server 2005 I have a column with a "DEFAULT" constraint. I'd like to create a script that drops that column. The problem is that is returns me that error : Msg 5074, Level 16, State 1, Line 1 The object 'DF__PeriodSce__IsClo__4BCC3ABA' is dependent on column 'IsClosed'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE D...
Is there a command, or a set of tables I can look at to determine which tables, stored procedures and views in SQL Server server 2005 have a certain user defined data type? ...
The following exception is thrown: Error Message: Microsoft.SqlServer.Management.Smo.FailedOperationException: Drop failed for Database '4d982a46-58cb-4ddb-8999-28bd5bb900c7'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Dat...
I am currently using SQL Server Management Studio (Ver 9.00.3042.00) and click the "New Query" and wrote the following code: Select colA, colB, colC colD From table1 When I click the parse (checkbox) button to validate the SQL statement, it says "Command(s) completed successfully". Isn't the parse button suppose ...
I'm creating a log shipping configuration between two sql server 2005 instances. I'm shipping the logs of a single database and I'm not using a Monitor service (for the time being). When I run the SQL Server Log Shipping Wizard on the database, the script executes as it should and claims there are no errors. But when it's complete, th...
I'm trying to import some data from Excel to SQL Server, I have a TEXT column with some numbers in it, some of the numbers go over just fine, and some turn into scientific form. Column in Excel is "text" type, column in the target sql table is varchar(255) Here are some examples: Excel [text] -> SQL Server [varchar(255)] 03138522...
I've built a entity framework model against a 2008 database. All works ok against the 2008 database. When I try to update the entity on a 2005 database I get this error. The version of SQL Server in use does not support datatype 'datetime2 I specifically did not use any 2008 features when I built the database. I can't find any ref...
How can I rename a schema in SQL Server 2005? ...
I'm interested in hearing people's thoughts about the pros and cons of database mirroring vs. log shipping in this scenario: we need to setup a database backup situation wherein there is exactly one secondary server that need not automatically pick up when the primary fails. Recovering and starting with the secondary should not have to ...
I have a couple of tables which are used to log user activity for an application. The tables looks something like this (pseudo code from memory, may not be syntactically correct): create table activity ( sessionid uniqueidentifier not null, created smalldatetime not null default getutcdate() ); create table activity_details ( ses...
It is my understanding that the default behavior when creating a table in SQL 2005 is that it will be created with dbo as the owner of the table. Is there a way to change this default behavior so that all tables get created as the user instead of as dbo? I am working on moving an application from SQL 2000 to SQL 2005 and much of the log...
For the last couple hours I have been messing with all sorts of different variations of SQL Server full text search. However I am still unable to figure out how the ranking works. I have come across a couple examples that really confuse me as to how they rank higher then others. For example I have a table with 5 cols + more that are not...
I have a database with one table, like so: UserID (int), MovieID (int), Rating (real) The userIDs and movieIDs are large numbers, but my database only has a sample of the many possible values (4000 unique users, and 3000 unique movies) I am going to do a matrix SVD (singular value decomposition) on it, so I want to return this databa...