i need to do this : There is a table called table1 it has a employee id column,status column which has values 1 and 0 only and a department column with values 100,101,102. i want to list all employeeid with the status = 0 from the above list i want to filter the employees which having (department=100 and whose status=1) Extended que...
I have a DTS package that runs on our MS SQL Server 2000 database server, and need a way for users to execute it from their own machines. (The package creates files that are then transferred by my application to the client machine.) I have included the "Microsoft DTSPackage Object Library" COM reference in the application, but still ge...
Hi, I need to create an entirely new Sql Server 2008 database and want to use a Database Project in Visual Studio 2010 (Ultimate). I've created the project and added a table under the dbo schema. The table .sql is shown only as plain text, though with colors. It has no designer, no Add Column, and no autocomplete. Existing column's pr...
Hi, Say I have a very long table (~35 million rows) called TimeCard with only 5 columns (tableID, CompanyID, UserID, ProjectID, DailyHoursWorked, entryDate). This is a pretty straight forward table that records employees' worked hours per day per project per company. I now need to generate a report to find out the employees' total wo...
I've got a table and the only time I'll be selecting or doing anything against it is to find all the rows where a certain date column is null. From there, I do stuff with it and update that column, and likely I'll never visit that row again, with the exception of auditing purposes. So, is there a best practice to ensure that the rows w...
I'm getting a timeout error when trying to execute a LINQ (-to-SQL) query System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Now, this is not just a case of a slow query: I run the equivalent SQL in SQL Management Studio and it com...
I have a simple procedure that selects 1000 rows from a table. For the sake of clarity, here's what the stored procedure looks like: alter procedure dbo.GetDomainForIndexing @Amount int=1, @LastID bigint, @LastFetchDate datetime as begin select top (@Amount) * from DomainName with(readuncommitted) where LastUpdated > ...
OMG! What am I doing wrong? declare @WTF TABLE ( OrderItemId int ) SELECT TOP 20 OrderItemId as OrderItemId INTO [@WTF] FROM ac_OrderItems SELECT * FROM [@WTF] Problem A: This creates a PHYSICAL table called @WTF. WHY?? I thought this was in memory only?! Problem B: The last line of code, if I do select * from @WTF... WITHOUT the ...
I am passing an ad-hoc Insert statement from c# application to the sql server 2000/2005. Sometimes, if machine (where sql server is installed) datetime format is different than what I am passing in, it is throwing an error. e.g. : In Insert statement I am passing '2010-03-10 00:00:00-05:00' this but machine regional date setting is dif...
I'm using SQL Server 2005. Let's say I have a table for products and another table for prices so that I can track price changes over time. I need a query that fetches distinct products (easy part) plus each product's most recent price and the date it changed. Products Table: CREATE TABLE [dbo].[Products]( [ID] [int] IDENTITY(1,1)...
I have a requirement to determine the maximum Id int value for a set of tables in my database. The column is always 'Id' and is the primary key. Is there a simple way I can make this determination without resorting to a cursor or looping? ...
I'd like to write a stored procedure in SQL 2005 to script all my database objects to a file. The only problem is...I have no idea how to do it. Can anyone help? Thanks! ...
A multitennant database for use by telsales agents, a table contains the prospect leads to call. One record is displayed to the agent at a time for him to call, after the call the agent updates the record with a coded call outcome taken from the outcome table. In this table some outcomes have a recycle time, after which the record should...
I have a table Title Name Type ------------------------------------------------ T1 A Primary T1 B Primary T2 B Primary T2 C Secondary T2 D Secondary I need the output t...
In the ASP.Net application I am writing I need to be able to have it send various faxes out. My fax server allows me to send the fax via an HTTP POST and returns to me an ID for the submitted fax. I need to be able to know if each fax ultimately sends OK or fails for some reason. The fax server allows me to query the status of a fax by ...
I have a SQL server and couple Windows clients and cache of some tables as objects on clients. I currently have a pull mechanism where every one minute or so clients query one row in DB to understand if cache is still good if changed they sync everything, but I want to change this mechanism to push based. I mean I want server to “ping” ...
I need to add a NOT NULL column to an existing (populated) table that will be a foreign key to another table. This brings about two problems: When you add the column, its value cannot be null - using a default is not an option (unless it is removed later on) because the database logic is used in the server side validation when a user e...
I have a LINQ to SQL query that's created dynamically. Funny thing is, when I run it in SQL Management Studio it's lightning fast. When I run it from L2S it becomes awefully slow after a while. This is probably because of the query plan/execution path. When I restart SQL Server the L2S query is also lightning fast again. Now with T-SQL...
I have a tricky problem that has had me scratching my head for a little while. I have some data that represents the delivery of "widgets" over a variable number of days, broken down into half hourly slots. For example (apologies for formatting - haven't quite got to grips with it): Date Time NoOfUnits 01-Mar-2010 00:00...
Hi, Is it possible to find out how big the data is in KB or MB for individual columns in a table? I have a script which tells me the physical size of each table, but I would like to know how much of that is taken up by certain columns in the database, especially when I have XML stored in a column. Any help much appreciated Cheers ...