Just curious what is the best practice for solving the following issue. I have institutions, accounts and users tables. Institutions and accounts have a one-to-many relationship. Institutions and users have a one-to-many relationship. Users and accounts have a many-to-many relationship or alternatively they could have a many-to-al...
I am multiplying the values of a few decimal columns together in SQL Server 2005 in a stored procedure. The SP already has tens of calculations. The problem is that there's precision being lost. Instead of the result of the calculation having precision of the largest precision involved in the calculation, it's 6 decimal points. I don't...
I am using this condition as part of my where clause: exists (select * from PRPB PB where PA.mid = PB.mid and (@inpo is null or PB.inpo = @inpo)) order by price. While testing non-null @inpo values, I noticed the query runs much faster when I instead use this condition: exists (select * from PRPB PB where PA.mid = PB.mid and (PB.inpo = ...
I am having trouble using the output cache param in an aspnet 2.0 page directive. I am using a session variable to hold the value of the selected image. It seems that the dynamic control in a datalist, is not working when the output cache is set to true in the page directive. Is there a way to cache the images seperately to avoid using a...
Hi all, I have downloaded SQL SERVER MANAGEMENT STUDIO but SQL SERVER AGENT is not found. Should I install it separately? Can anybody tell me the link where SQL SERVER AGENT is availalbe as a Freeware to download it? Please help me out I am in urgent need. Thanks in advance! ...
MyTable.Update(long.Parse(id), txtLocation.Text, txtCode.Text, locale, chkAvailable.Checked, DateTime.Now); Neither chkAvailable.Checked or DateTime.Now cause an update but changing the other fields do... What's going on? I'm using SQL Server 2005. .NET 3.5 SP1 Rob ...
How do I convert a RDL file that was created using the SQL2008 designer to work on SQL2005 Reporting services? ...
How do I alias the inserted and deleted virtual tables in a trigger in MSSQL 2005 so that I can use another set of inserted and deleted virtual tables from an OUTPUT clause later in the trigger? I need to alias these tables in a trigger as per http://msdn.microsoft.com/en-us/library/ms177564%28SQL.90%29.aspx. [Edit] I should have been...
I need to rename all of my tables, stored procedures and obviously the code within each stored procedure that was referencing the old table names. Why is the best way to do this? Some methods I have considered: SP_Rename - Gets half the job done. However this doesn't change the code within the SP itself In addition to RedGates' Refa...
I have a column in my SQL-2005 database that used to be a varchar(max), but it has been changed to an nvarchar(max). Now I need to update my hibernate mapping file to reflect the change, and this is what it used to be: <element type="text" column="Value"/> When I try to run the application, the following error appears: org.hib...
I much prefer to code in t-sql using what is in effect an in-line join, rather than have a long list of the joins at the end of the stored procedure or view. For example, I code: SELECT PKey , Billable, (SELECT LastName FROM Contact.dbo.Contacts WHERE (Pkey = Contacts_PKey)), (SELECT Description FROM Common.dbo.LMa...
This is somewhat of a sequel to Slow Exists Check. Alex's suggestion works and successfully avoids code repetition, but I still end up with a second issue. Consider the example below (From AlexKuznetsov). In it, I have two branches to handle 1 contraint. If I had 2 optional constraints, I would end up with 4 branches. Basically, the...
Thus far all I have done with SQL are simple select statements, but now I've also started learning about INNER JOINs, GROUP BY clauses and inner queries. I learned the concepts, but now I’d like to do some exercises using the Northwind database, so that such queries would become second nature. I tried to come up with good examples tha...
Consider the scenario where a database has a SQL Database Role or Application Role. The task is to grant Execute permissions to n stored procedures. When using SQL Management Studio, there's a nice screen to help apply permissions to objects for a Role. Here are the steps to apply permissions: select the object that you want to gra...
I am trying to insert 61,000+ objects, obtained via a remote call, into a SQL Server 2005 database in the fastest method possible. Any suggestions? I have looked at using SQLBulkCopy, but am having a few problems working out how to get the data into the right format since I am not starting with a DataTable, but instead have a list of ob...
Once your databases are converted from SQL Server 2000 to SQL Server 2005, is there any way to compare DTSs on two servers to see if they are still essentially the same? I need to see if my dev and prod are the same, and comparing them manually is really time consuming. If they were still in 2000, I could use the Red-Gate tool DTS Comp...
I'm after a simple stored procedure to drop tables. Here's my first attempt: CREATE PROC bsp_susf_DeleteTable (@TableName char) AS IF EXISTS (SELECT name FROM sysobjects WHERE name = @TableName) BEGIN DROP TABLE @TableName END When I parse this in MS Query Analyser I get the following error: Server: Msg 170, Level 15, State 1, Proced...
i have been working on query which uses compute by clause for avg and sum operators . As Microsoft declared this will be a discontinued feature ,what will be the replacement for this feature ...
I have a view from which I am selecting three columns. Of these three columns, one of them contains the OS version. I want to create an additional column in the result which checks the OS Version. If the OS Version is less than 5.1 it should return 'Bad', if it is greater than that it should return 'Good'. Any ideas about how to add t...
How do you programmatically add an SSIS ForEachLoop? We are limited to SQL Server 2005. I'm having trouble adding the following properties: ForEachEnumerator CollectionEnumerator Directory FileNameRetrieval FileSpec Recurse My code format looks like this: Microsoft.SqlServer.Dts.Runtime.Executable executable = aSequence[0].Executable...