This is puzzling me - in SQL Server 2005/2008, I can specify the default file group which is then used for all data tables. I can also specify another filegroup for all BLOB type fields. But I cannot figure out if and how I can specify a default filegroup for indices..... My setup usually is: * primary filegroup - nothing but the syste...
I have similar code for vb and .net which runs my SSIS package just fine. The problem is the c# code and .net does not run my .dtsx package. I get all sorts of errors in cluding not being able to find the file on my desktop. I tried remotely running the package and locally. Is it my code. I am not sure what to do next. I do have all the ...
I am trying to retrieve the largest number from a varchar column that includes both numbers and strings. An example of the data I'm working with: BoxNumber 123 A5 789 B1 I need to return the largest number (789 in this case) from the column while ignoring the non-numeric values of A5 and B1. I have found solutions that use custom func...
I have a nvarchar(max) column in a sql server 2005 table which is used for storing string representations of .NET TimeSpan objects. Sometimes the table is manually edited. I want to add a check constraint to verify that the string can be parsed by TimeSpan.Parse(). How should I do this? I guess I could use one of the methods for enabling...
Most examples dealing with table partitions, create the table on the partition scheme. For example: create table SomeTable ( Id int not null , DueDate DateTime not null ) on MyPartitionScheme(DueDate) Where MyPartitionScheme is a predefined partition scheme. If I have a table that already exists, possibly with data with it. Can...
I am trying to query for a list of stored procedure definitions using information_schema.routines that exist in one database but not in another. SELECT t1.Routine_Definition FROM [server1].MyDatabase.INFORMATION_SCHEMA.Routines t1 LEFT JOIN [server2].MyDatabase.INFORMATION_SCHEMA.Routines t2 ON t1.Routine_Name = t2.Routine_Name WHE...
Hi! I'm inserting a large amount of rows into an empty table with a primary key constraint on one column. If there is a duplicate key error, is there any way to find out the value of the key (or row) that caused the error? Validating the data prior to the insert is sadly not something I can do right now. Using SQL 2008. Thanks! ...
When using multivalue parameters in sql reporting services is it more appropriate to implement the list filter using a filter on the dataset itself, the data region control or change the actual query that drives the dataset? SSRS will support any scenario, so then I ask, is there a reason beyond the obvious of why this should be done a...
Hi, are there performance issues when retrieving and filtering data from views than from tables by using a stored procedure? Simplification: Is it faster to retrieve or filter data from views using stored procedure than getting or filtering data from tables using stored procedure? ...
I have a stored procedure and if the stored procedure does this: SELECT 0 As Ret DELETE FROM table where value1 = 1 Returns 1 row result with its value of 0 and column name Ret But if I do this: DELETE FROM table where value1 = 1 SELECT 0 As Ret I get no returned results. My question is, how do I get the second variation to retu...
This is the situation: I have a Trigger.dll and a Trigger.XmlSerializer.dll. I use CREATE ASSEMBLY to register them in MSSQL. Now, I have compiled new versions of both. I want to use ALTER ASSEMBLY to update them, however you can only update one at a time. If you try to update one that has a dependency, it complains. What's the tr...
I need to design a Key/value table in my database and I'm looking for guidance on the best way to do this. Basically, I need to be able to associate values to a dynamic set of named properties and apply them to an external key. The operations I need to be able to support are: Apply a key/value pair to a group of items Enumerate all ...
Does it have any sense (except of server side validation XML/schema/dtd) to store XML in XML type instead of text/varchar/ntext? I'm not planning to do any XML manipulation on database side. Purpose of my investigation is to decrease database size. Can a using an XML data type for untyped XML for the purpose? Which other pros and cons i...
I am working on a Database that uses the Float data type to store values that should only be 2 decimal positions (dollars and cents). Using Float appears to work OK, as long as the person updating the Float column does a ROUND. Of course, this does not always happen and then when a SUM is done it is always off a few pennies from what is ...
I have a number of stored procedures I call from code with ExecuteNonQuery. It was all good but 2 of my stored procedures started timing out intermittently today with: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated. If I e...
I have implemented a linked list as a self-referencing database table: CREATE TABLE LinkedList( Id bigint NOT NULL, ParentId bigint NULL, SomeData nvarchar(50) NOT NULL) where Id is the primary key, and ParentId is the Id of the previous node on the list. The first node has ParentId = NULL. I now want to SELECT from the t...
I have a SQL Server 2000 DB with a lot of PK/FK relationships changes in a specific time frame. Is there any way I can retrieve all the relationship changes or additions to a Database? I tried this query which returns all the ForeignKeys. SELECT f.constid, OBJECT_NAME(f.fkeyid) AS 'FKTable', c1.[name] AS 'FKColumnName', OBJECT_NAME(f.r...
I think I should mention I'm trying to get Entity Framework\SQL server to do something that I was used to coming from Rails. I really just want to know the 'best' way to have automatic created_at & updated_at column values for records that I insert/update in the database. Right now I've hooked into the ObjectContext.SavingChanges event...
I want to analyze an SQL Server stored procedure from .NET code to retrieve metadata for the data/columns it returns (not OUTPUT parameters). Like when you drag/drop a sp on a DataSet i VisualStudio and it automatically generates columns. It could be useful for me for some code generation I'm testing out. The procedures contains simple...
I am configuring a new environment to run several intranet web applications. I have 2 servers, one will be the SQL Server 2008 server, and the other will be the IIS server. I also need to install SQL Server Reporting Services. I am not sure whether it would be better to run reporting services on the database server, or web server. Is...