I am using migrator.net to handle database migrations for SQL Server, and I like it so far. The only issue I've run into is how best to migrate changes to views. Thus far, I've been managing each update as a separate script file (which recreates the view). This lets me go back to previous versions of the view without duplicating the c...
I'll begin by admitting that my problem is most likely the result of bad design since I can't find anything about this elsewhere. That said, let's get dirty. I have an Activities table and an ActivitySegments table. The activities table looks something like: activityid (ident) | actdate (datetime) | actduration (datetime) | ticket...
Currently we have MS SQL Server 2005 (32 bit). We have 1 assembly (and only 1 assembly) which we use for encryption and decryption. Only 512 MB of system memory is allocated to CLR. The assembly runs pretty slow, and I'm trying to rule out if its from memory or not. When I run the SQL code in query analyzer (not in an assembly) it runs q...
I'm running sql analyzer on the following query SELECT bl.Invoice_Number, bl.Date_Invoice, ti.TranNo, bt.Description, CONVERT(decimal(15,2), bl.Invoice_Amount) AS Invoice_Amount, co.Company_ID, co.Account_Nbr, isnull(bl.Reference,' ') as Reference, bl.Billing_Log_RecID AS BillingKey FROM [CONN.domain.NET].cwwebapp.dbo.Billi...
Here's what I want to do: Given a table PeopleOutfit (id int primary key, boots int, hat int) And a stored procedure UpdateOutfit @id int, @newBoots int = null, @newHat = null Is there a way to tell whether I called this procedure as exec UpdateOutfit @id=1, @newBoots=null, @newHat=5 effectively telling that the person with id ...
Hi, I need to export a large amount of data (~100mb) from a sql table to a user via the web. What would be the best solution for doing so? One thought was to export the data to a folder on the db server, compress it (by some means) and then provide a download link for the user. Any other methods for doing so? Also, can we compress data...
I have a SQL script that populates a temp column and then drops the column at the end of the script. The first time it runs, it works fine because the column exists, then it gets dropped. The script breaks the 2nd time because the column no longer exists, even though the IF statement ensures that it won't run again. How do I get around S...
I have a table with a column whose values come from an Enumeration. I need to create a TSQL function to convert these values to "Friendly Names" upon retrieval. Examples: 'DateOfBirth' --> 'Date Of Birth' 'PrincipalStreetAddress' --> 'Principal Street Address' I need a straight TSQL UDF solution. I don't have the option of install...
What approach do you have towards creating and maintaining database indexes when using ORM such as NHibernate/Hibernate. Since the ORM is generating the queries, are there any tools you could recommend that could analyze query plans of those and suggest the kind of indexes that should be created? My current approach is ... wait until...
When I generate sql schema creation scripts manually I usually just call 'Create Procedure...', however I notice that when you generate the script using the Tasks/Generate Scripts option it uses 'spexecutesql @statement = ..' e.g. EXEC dbo.sp_executesql @statement = N'-- ============================================= -- Author: ...
My fellow programmers did not think to add timestamps to every tables of our outanding database... Result : some tables seems to be outdated, but it is very difficult to say. What I would really like is to get, for each table, the date/time of the last insert command performed against it. Is it any way to tell it ? Thanks in advance ...
How do I get the position of a given value inside a table column. I need to get the column number. ...
I want to find Top and botton 10% sales people.How can I do this using SQL 2005 or 2008? DECLARE @Sales TABLE ( SalesPersonID varchar(10), TotalSales int ) INSERT @Sales SELECT 1, 200 UNION ALL SELECT 2, 300 UNION ALL SELECT 7, 300 UNION ALL SELECT 4, 100 UNION ALL SELECT 5, 600 UNION ALL SELECT 5, 600 UNION ALL SELECT 2, 200 UNION A...
Have other people here played with SQL Server 2008 Compression at either the page or the row level on their datasets much? What have your impressions been on performance both speed and disk-space wise? Has anyone ever seen compression demonstrably hurt performance? On some of our huge fact tables we've been playing around and noticing...
Hi, In a asp.net/Sqlserver project, we create connections using ado.net (sql authentication) and we see a behaviour where there are a lot of active connections in "sleeping","Awaiting command" status The code does the following - Get a connection from common function, update db, Commit transaction, close & dispose transaction, cl...
Just looking at the requirements of a new project and I wanted to make sure this use case was sound: user fills in InfoPath (2003) form locally on their PC a button within the InfoPath form titled 'submit' brings up a new outlook (2003) email message with the infopath form attached. User presses sends and email is sent to an exchange m...
Hi, I'm a beginning web developer sitting on an ambitious web application project. So after having done some research, I found out about SQL Service Broker. It seems like something I could use, but I'm not sure. Since learning it requires someone to put in lots of time, I wanted to be sure that it would fit my needs. I need to implemen...
DECLARE @DateNow smalldatetime SET @DateNow='12:30' select @DateNow -------------------------------------OR-------------------------------------- select CAST( '12:30' as datetime ) Result: 1900-01-01 12:30:00.000 (i don't want this) But i need this result in time format not string not datetime? Result: 12:30 (i want this) ...
Hi, I have a java application which is doing multiple concurrent CRUD operations on a database. I'm adding support for SQLServer but am having problems with deadlocking during concurrent deletes. After some investigation it appeared that the problem may be due to lock escalation on a particular table. In an attempt to fix it, I decide...
I am looking for a way to retrieve the "surrounding" rows in a NHibernate query given a primary key and a sort order? E.g. I have a table with log entries and I want to display the entry with primary key 4242 and the previous 5 entries as well as the following 5 entries ordered by date (there is no direct relation between date and prima...