Suppose i am having stored procedures which performs Insert/update/delete operations on table. Depending upon some criteria i want to perform some operations. Should i create trigger or do the operation in stored procedure itself. Does using the triggers decreases the performance? Does these two tables viz Inserted and deleted exist...
An MS SQL Server 2008 BULK INSERT to a datetime column introduces an error in the 3rd decimal place of the seconds portion: 2009-09-19 15:02:41.328 in the input file becomes 2009-09-19 15:02:41.327 in the database. Here is what the input text file contains: 1 2009-09-19 15:02:41.328 Here is what the table looks like after BULK INS...
What is a good,stable and preferably free alternative to SQL Server Integration Services? I'm so tired of this buggy piece of software. ...
I've got the following query that uses a full-text index to search for the TOP 5 products (from the RawProducts table) matching the query, in a given Shop (populated by the @ShopId variable). At the moment I'm calling this procedure over and over again for every ShopId (there are 27 Shops) - which is a bit slow. My question is - could ...
Guys lease help me out I think I'm close, but I'm missing something. Background I'm rewriting a SQL CLR assembly (stored procedure), my assembly contacts another resource and gets back XML. I want to return this XML as a recordset, NOT a scalar value. From what I've seen, this is how to return a recordset: SqlContext.Pipe.Send(mySqlD...
For some integration tests I want to connect to the database and run a .sql file that has the schema needed for the tests to actually run, including GO statements. How can I execute the .sql file? (or is this totally the wrong way to go?) I've found a post in the MSDN forum showing this code: using System.Data.SqlClient; using System.I...
If I have this data in a table in MSSQL which represents a horizontal row of objects and their coordinates: row id value 35395 776130 0 35395 776131 20 35395 776132 40 35395 776133 60 35395 776134 80 35395 776135 100 35395 776136 120 35395 776137 140 35395 776138 160 35395 776139 180 35395 776140 200 An...
Hi In my app, any user can configure which columns to show, reorder and resize columns. To save every user setting, I have a few tables for that. One of them is called UserColumns, and looks like this: Id | UserId | ColumnName | DefaultWidth | Width | SortOrder Is there any way, without dynamically generating the select string and e...
Tried this in as connection string connectionString="Server=.\SQLEXPRESS; AttachDbFilename=E:\Database\dnn49.mdf;Database=dnn49; Trusted_Connection=Yes;" but i get an error Unable to open the physical file "E:\Database\dnn49.mdf". Operating system error 5: "5(Access is denied.)". Cannot attach the file 'E:\Databas...
I've got the following query, that looks up the TOP 5 Products matching the search. Each Product is associated with a Shop SELECT TOP 5 * FROM Products p, Shops s WHERE p.ShopId = s.ShopId AND p.ProductName LIKE '%christmas%' I need to extend this so that it returns me the TOP 5 Products in each Shop. Could any...
Hi, I am using VB.net code and SQL server 2005. I am havng below code for sending email in my vb.net code. Protected Sub ibtnSendInvites_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ibtnSendInvites.Click Try Dim emailList As New List(Of String) For Each curRow As GridViewRow In Gr...
Hi, I've got the following query (run in an sproc): DECLARE @BrandId uniqueidentifier SELECT * FROM Products p WHERE p.BrandId = @BrandId My question is - when NULL is passed into @BrandId the query (correctly) returns no results - is there a way of telling SQL to return all rows when NULL is passed into @BrandId (without usi...
Hello! I got a few stored procedures that uses a TRY/CATCH statement, so i execute the main program and if it generates any errors i catch them. Now my problem is in the catch statement, i have this piece of code: BEGIN TRY INSERT INTO ContentTypes (ContentName, ContentPath) VALUES (@ContentName, @ContentPath) SET @QRe...
I have the following query, which performs a full-text search (CONTAINSTABLE) on the Products table, and returns 1 record. Each Product is referenced by the ShopProducts table (each ShopProduct represents a product in a shop and is has a foreign key to Products.ProductId. Each row in the ShopProducts table has a ShopId column. My quest...
I am using SQlServer 2008, and an extract of some datatables is displayed below: Users Id (PK) UserItems UserId (PK) ItemId (PK) - (Compound key of 2 columns) ... UserItemVotes UserId (PK) ItemId (PK) VoterId (PK) - (Compound key of 3 columns) I have the following relationships defined: User.Id -> UserItems.UserId (UserItems.Use...
Hello! I got this, its calling a SP in my MS SQL 2008 database: [Function(Name = "dbo.Content_GetContent")] [ResultType(typeof(Content_GetContentResult))] [ResultType(typeof(Content_GetContentImagesResult))] [ResultType(typeof(Content_GetContentBoxesResult))] [ResultType(typeof(Content_GetContentSearchWordsResult))] ...
I'm trying to create a foreign key in a SQL Compact database but I keep getting the error message "A foreign key value cannot be inserted because a corresponding primary key value does not exist." TableA is referencing TableB already and I'm trying to create a reference from TableC using the same primary key in TableB. Since I already h...
Hi all. I have a coding problem where I have to store a 'note' and the dates the note applies to. Think of: Note 1 Hi, today Mr Client rang arranging these appointments. 30/8/2009, 31/8/2009, 5/9/2009 Note 2 Business as usual. 30/8/2009 Note 3 Restaurant is shut. 6/9/2009 I need to store the following data in a database, while maint...
Hi, I am using VB.net (FormView and ObjectDataSource) and Sql Server 2005. I want to get last inserted @@identity in table on FormView1_ItemInserted Protected Sub FormView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles FormView1.ItemInserted End Sub My issue is that I w...
I created a trace file of a days work (~10 hours) at a client and ran it in the SQL Server tuning advisor. The tuning advisor ran over the weekend and the report says: Time taken for tuning: 2 days, 13 hours, 58 minutes Number of events in workload: 1762519 Number of events tuned: 67525 Number of statements tuned: 37258 I also summe...