Next query runs successfully: select top(100) * from PackageSessionNodes where Cast(ContentInteractions as nvarchar) != '' Next gives me error Target string size is too small to represent the XML instance update PackageSessionNodes set ContentInteractions = '<contentinteractions />' where Cast(ContentInteractions as nvarchar) = '' ...
Suppose I have a view that rolls up data via a GROUP BY clause: CREATE VIEW InvoiceAmountsByCustomer AS SELECT Customer.CustomerID, SUM(Invoice.TotalAmount) AS TotalInvoiceAmount FROM Customer LEFT OUTER JOIN Invoice ON Customer.CustomerID = Invoice.CustomerID GROUP BY Customer.CustomerID Now, suppose I want the view...
I want to create trigger for one table for insertion...If i insert records in one table then that same records should be inserted to another one......how? and also explain about triggers ...
Is there a command in Microsoft SQL Server T-SQL to tell the script to stop processing? I have a script that I want to keep for archival purposes, but I don't want anyone to run it. ...
Does the SQL Server decimal data type stored the actual number of digits after the decimal for each value in a column? For example UPDATE [Mixes] SET [Concentration] = 0.0012500 WHERE [Id] = 1 where Concentration Decimal(21,11) Is it possible to retrieve the exact "0.0012500" value as entered by the user, or is it necessary to st...
I have an analysis services cube in SQL server 2005 which I'm connecting to via an excel front end. When I connect via one user its fine, but when I log on to the same machine as another user I get an error in my excel spreadhseet - "user...does not have access to the [Cube name] database" Obviously the first user has the correct permi...
I've got two matching php programs, one held on the development folders, the other on the live folders, both on the same machine. The live version can pull data from the database and display it, the development version cannot and kills the page as solid white. Both are identical and both are reaching for the same database. I wish I ...
I have two tables that contain data from different temperature sensors: Table1 _TimeStamp Temperature 2009-12-20 11:59:56.2 10.1 2009-12-20 11:59:56.3 10.2 2009-12-20 11:59:56.4 11.0 2009-12-20 11:59:56.5 Null Table2 _TimeStamp Temperature 2009-12-20 11:59:56.2 10.5 2009-12-20 11:59:56.5 9.8 2009-12-20 11:59:56.7 12.0 2009-12-2...
Hi, I am having a problem with a trigger in SQL Server 2005, I have created the trigger and I have tested it inserting rows manually and it works fine that way, however I call a Stored Procedure from a c# web application and the triggers does not get fired, so I took same data inserted from the web application, deleted that row and reins...
We're looking to provide a fuzzy search on an electrical materials database (i.e. conduit, cable, etc.). The problem is that, because of a lack of consistency across all material types, we could not split sizes into separate fields from the text description because some materials are rated by things other than size. I've attempted a co...
Is there a way to query TFS database to get the last 10 check-in details The output should be something like File name | Comment | Changed By | Date ---------------------------------------------------------------------------- Test.cs Added new functionality username 01/08/2010 I ...
I have a high-demand transactional database that I think is over-indexed. Originally, it didn't have any indexes at all, so adding some for common processes made a huge difference. However, over time, we've created indexes to speed up individual queries, and some of the most popular tables have 10-15 different indexes on them, and in som...
I'm trying to write a unit test for the database layer that validates the connection string's user has update/insert/read permissions. insert/rollback would create extra gaps in the identity column or launch triggers. for read permissions selecting against the table creates load/work on the sql database and updates when the table was las...
Suppose you have a table with a Type 2 slowly-changing dimension. Let's express this table as follows, with the following columns: * [Key] * [Value1] * ... * [ValueN] * [StartDate] * [ExpiryDate] In this example, let's suppose that [StartDate] is effectively the date in which the values for a given [Key] become known to the system. ...
Hi all, I'm having some trouble with the following sproc Create PROCEDURE GetMatchingUsers @id int = NULL, @lastName varchar(50) = NULL, @firstName varchar(50) = NULL AS BEGIN SET NOCOUNT ON DECLARE @q nvarchar(4000), @paramlist nvarchar(4000) SELECT @q = 'SELECT Id , LastName , FirstName ' SELECT @q = @q + 'FROM User...
Is there a way to suppress warnings generated by inserting duplicate keys on indexes with IGNORE_DUP_KEY = ON? The warning it gives is "Duplicate key was ignored." I am investigating using this option with a table variable for performance reasons, but it generates a lot of warning messages. This can be a problem since I log the output...
I know that using GUID generated by newid isn't a good candidate for primary key for performance issues. How about a composite primary key with {newsequentialid(), newid()} so a new GUID is guranteed to be greater than the one generated previously? Is there a performance issue here too? You may think why would anyone do this but i am ...
I have a web project GUI.. I was first working with administrator only. So when the administrator logs in with his username and password i use forms authentication to redirect him to the default page "Default.aspx". But now i have to work with Guests also... and on login check the role if it is of a guest then redirect him to a gues...
I have in my select clause: AVG (cast(scale as decimal(5,2))) I keep getting a number like: 0.6523412897, nothing I do seems to get me to my desired: 0.65. Basically I want to have a scale of 2 (two decimal places). Thanks ...
I have 2 tables: NewTable (partNumber, html) OldTable(partNumer, html) The old table has duplicate data i.e. rows with the same partNumber and html. NewTable is empty. I want to take the rows from 'OldTable' and insert them into NewTable. The only condition that I get any row from 'OldTable' where the html column is not an empty st...