sql-server

SSIS error information

I have 15-20 data task each in their own sequence container in a single SSIS. Is it possible to catch the data task or sequence which failed and email that details (task name or sequence name, cause for failure) to a user? ...

Architecture that displays messages (like downtime) in application

I'm thinking of a architectural way of displaying messages in our application (Flex-Asp.NET-SqlServer), mostly messages that announce for instance a downtime. Currently I was thinking of creating a table FlexMessage that holds the name of a message (based on that name I now where to put in Flex) and the value (the message itself). As a ...

Need help with a complex SQL query

I have a stored procedure that uses this select statement: SELECT dbo.VendorProgram.id, dbo.VendorProgram.CODE, dbo.Programs.ProgramName + '-' + dbo.Divisions.Division + '-' + dbo.Vendors.Source + '-' + dbo.Mediums.Medium + '-' + dbo.VendorProgram.content AS SourceDe...

Calling Linked Server Stored Proc and retrive OUTPUT Parameters?

I have Linked Server to Oracle in SQL Server 2005. There 2 Stored Procs defined. How can I call them in SQL server. One Stored Proc has two OUTPUT parameters. I need to get them in SQL server call Linked server name is TEST. below are stored procs. I need these SPs separately. check_lockout(v_id_in, v_out_result, v_lockout_msg); upda...

Merge two field into One with only new items

I have two tables which both contain 'pipe separated values' e.g: Table 1: DataField_A item 1|item 2|item 3|etc..... Table 2: DataField_A item 7|item 5|item 3|etc..... I need to merge Table 2 into table 1 such that Table 2 contains all items across both tables. Doing this pro-grammatically would be a simple matter of loop...

database,sql server, sql which key?

I have 1 table with 6 columns lets call them A B C D E F They can be null or a value but that once have a value entered in any row of any column it must be null or unique and validated by the database what key or constraint could I use to achive this? ok real life scenario-- I have a product this is carrots the carrot can have upto 6...

How to troubleshoot sql server performance problem

Ok this general problem has reared its ugly head twice in the past 6 months (different stored procedure). We have our internal users report a timeout error in the application. We can reproduce the problem in the application in a controlled environment. So we go through the normal steps of checking for blocking using sp_who2. Everything l...

Why is test for existing column failing in SQL?

I have this SQL change script that runs as part of my nant orchestrated DB creation or update: SET XACT_ABORT ON BEGIN TRANSACTION PRINT 'Change MyColumn column to MyNewColumn column in MyTable table' IF EXISTS (SELECT * FROM sys.columns WHERE Name = 'MyColumn' AND Object_id = OBJECT_ID('[dbo].[MyTable]')) BEGIN ...

Multiple application using one database?

Hi Think Design: I have many applications that share the same user database! Other tables are shared as well such as user activity logs, purchases etc 1) Anyways my question is if I was to make all applications just use 1 database for everything! Would I have any problems with scalability? Or any other problem doing this? Is it better ...

SQL Conditional JOIN

I have three tables 'Employees', 'Departments' and 'EmployeesInDepartments' The 'Employees' tables references the 'Departments' table (Each employee must have a DepartmentId). However, an employee can exist in multiple departments by adding entries (EmployeeId and DepartmentId) to 'EmployeeInDepartments' table. I currently have the foll...

SQL Server Clustering limit?

Can you do SQL Server Clustering of more than 2 servers? Master can have more than 1 slave? What is the limit? ...

Will Microsoft SQL Server efficiently handle a non-persisted computed column?

I'm having a hard time phrasing this question in such a way that doesn't turn up results for persisted, indexed computed columns. My question is, if I have a table such as: CREATE TABLE Customers ( ID int, Name nvarchar(50), Balance money, HasBalance AS CONVERT(bit, CASE WHEN Balance > 0 THEN 1 ELSE 0 END) ) Assuming ...

SQL - How to Find YTD amount?

Let's say I have a table like this called hours: employee, payperiod, hours greg, 1/31/2010, 12 greg, 1/31/2010, 15 mark, 1/31/2010, 10 mark, 1/31/2010, 11 greg, 2/28/2010, 12 greg, 2/28/2010, 15 greg, 2/28/2010, 4 How would I find the year to date hours for a given employee and payperiod? For the above example I'd want to get to: e...

Unable to step into stored procedure inside stored procedure - SQL debugging using VS2010 and SQL Server

I'm checking out the SQL Debugger using Visual Studio 2010 (connecting to a SQL 2005 Enterprise Server), and I can successfully debug a stored procedure by going to Server Explorer -> Right clicking on the procedure -> "Step Into Stored Procedure". However, once I'm in, I can't step into or set any breakpoints inside any subsequent func...

Tsql - get entire row information with max and group by

I have a table, with the following columns: PK1 PK2 ID DATE Value flag I do a calculation that involves taking the max value per ID. select id, max(value) from table group by id I want to mark the flag on the rows that I am using. If id and the max(value) correspond to multiple rows flag the one with the...

T-sql range Query

I want to do something similar to below. I have the pseudo-logic below. Just wonder how to implement this: delete B from TableA A with (nolock), TableB B with (nolock) where A.BeginNumber <= B.startipnum and A.Endnumber >= B.endipnum delete A from TableA A with (nolock), TableB B with (nolock) where A.BeginNumber => B.startipnum and ...

Displaying Multi-Value Parameters.

Is this really the best way to handle this? I have a multivalued parameter which has about 25 elements in it. If the user selects "Select All" and I use the standard (at least as far as I know) method of displaying the parameter at the top of the report: =join(Parameters!ProductClass.Value, ",") Rather than the the word "All" or some...

SQL Server 2000 error

When I run my .net app connected to SQL Server 2000, I get the error "invalid Floating Point Operation". I did search for the error cause http://fugato.net/2005/02/08/sql-server-nastiness found this link which says there may be Bogus Data in one of the columns. I have a backup from a month old data, when I connect to the old database it...

SQl Server: Add seconds to a datetime field?

This should be a softball for you SQL guys. I know I can add to an int field with something like "UPDATE tblUser SET Total=(Total+2)" but what is the syntax for adding seconds to a datetime field? I'm using SQLServer 2008 ...

Best practice for operating on large amounts of data

I need to do a lot of processing on a table that has 26+ million rows: Determine correct size of each column based on said column's data Identify and remove duplicate rows. Create a primary key (auto incrementing id) Create a natural key (unique constraint) Add and remove columns Please list your tips on how to speed this process up ...