I have parameters to a stored procedure that may be null or have a value. In the case of a null value the search is not to be using that parameter. Is there a way in the where clause to define it so the where clause can take a parameter what ever its value and for the null not restrict the s...more >>

Hi all I have a table like this Code Value 1 1000 2 2000 3 -500 I want to write a query that returns like following output: Code Value Total 1 1000 1000 2 2000 3000 3 -500 2500 I mean I want to sum a colum...more >>

Using SQL Server 2K TableA with two columns: StudentID Int (PK) ATTENDANCE char(7000) TableB has seven columns: StudentID Int ATTEND1 varchar(254) ATTEND2 varchar(254) ATTEND3 varchar(254) ATTEND4 varchar(254) ATTEND5 varchar(254) ATTEND6 varchar(254) I want to inse...more >>

I just read something surprising in my 2005 cert study materials: Sql Server cannot generate an execution plan for a cursor. Is that really true? I knew cursors were a bad idea, because they're not set-based, etc., but I didn't realize that no execution at all could be generated??...more >>

I have set up a trigger to update a sister table on a linked server. Both SQL 2000. All seemed OK until, whilst testing, I got an error where it said it could not set up a distributed transaction. The error cleared after a while (presumably the network sorted itself out) but I can't implement the...more >>

I have a table, with data: CREATE TABLE [dbo].[tblPivot2]( [ref] [varchar](15) NOT NULL, [Age] [smallint] NULL, [Zip_Abbrev] [varchar](3) NULL, [Onset_Age] [smallint] NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF INSERT INTO [tblPivot] ([ref] ,[Age] ...more >>

Consider the following table: ID dateField intFieldA intFieldB stringFieldA stringFieldB I want a query that will give me the row with MAX(dateField) grouped by ID. If I say: SELECT Max(dateField), ID FROM table GROUP BY ID; Then that will give me each I...more >>

I am trying to figure out which Sp my inherited server is on in sql 2005. select @@version gives ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------...more >>

#tmpA contains 1 column named code and 14 rows -- 6 'A's, 4 'B's, 4 'C's #tmpB1 contains 1 column named code and 2 rows -- 1 'A' and 1 'B' #tmpB2 contains 1 column named code and 11 rows -- 5 'A's, 2 'B's, and 4 'C's I need to select rows from #tmpA where the count of individual codes does n...more >>

Hi everyone I'm using Reporting Services and in my Dataset's Query I'm want to use a variable in my IN clause that that holds the different values (strings) but I can't seem to get it to work. I've posted the SQL statement as it currently is and what I tried to do to get it to work. Thanks fo...more >>

SQL Server 2005 How can I : check to see if an existing table is partioned? if not, partition it preserving data within it preferably using a transaction. Thanks!...more >>

Is it possible to accomplish following. create table Orders ( RowID int identity, Batchid VARCHAR(10), Product varchar(10) ) go insert into [orders](BatchID,Product) values('10X','Cycle') insert into [orders](BatchID,Product) values('10X','Book') insert into [orde...more >>

I have a CLR stored procedure project containing several stored procedures in C#.NET 2005 that is deployed to SQL2005. When I Deploy the project from Visual Studio.NET, all the permissions on SQL2005 are deleted for all of the stored procedures. I have to reset them every time I deploy a chan...more >>

I am doing a lookup on one DB to compare the results with a result set from another DB. I've implemented this look up via a View. The quiery doing the comparison is taking quite a while to run. I've got some constraints in that I cannot have these sitting on the same DB and I may not index th...more >>

Hi , I could not find the debugging featire in sql server 2005 managemnet studio. WHen I see visual studio 2005(which was installed while installing sql server 2005) the debug tool bar was disabled. Can any one tell us how to debug the sql code ? and clarify...more >>

Hi, I am from oracle background where we have a concept of database packages. A package is a group of related procedures and functions, together with the cursors and variables they use, stored together in the database for continued use as a unit. In sql server, I am trying to build a fun...more >>

Is there a way to do a "recursive join" (SQL Server 2005)? In other words, I'd like a table to link back to itself. For example, I have a single table that stores articles in multiple levels (treeview), how can I query all articles from a particular article? Let's assume that the article table...more >>

I have some SPs where I can pass a variable or not, such as the following: CREATE PROCEDURE dbo.GetTitles @Type varchar(80) = null AS SELECT * FROM Titles WHERE (@Type is null OR Type = @Type) GO This will either get me all the records or only those that match the @Type parameter, if ...more >>

Dear to Whom this Concern, Could some one learn me how I could update my table field via stored procedure? I have a stored procedure that generates my field data, and my table should be updated with this generated data from my stored procedure. Thank you, Rune ...more >>

I am trying to install "Reporting Services" on my laptop, but when I get to the point where I can check the "Reprting Service" its all grayed out I cannot select it. ANybody can tell me why this is happening and how I can resolve the issue. ...more >>

Is there any way to give xquery in sql server a hint as to how large the resulting document will be so that it preallocates more efficiently? ...more >>

Hi All I would like to delete all the fields with NULL OR empty values e.g. Create table Tb1 ( A int IDENTITY(1,1) ,B varchar(10), C varchar(10), D varchar(10)) insert into Tb1 (B,C) values ('a1','') insert into Tb1 (B,D) values ('b2','') insert into Tb1 (C,D) values ('','d3') --drop tab...more >>

Is it possible to use Openquery to access Foxpro data on a 64 bit Windows 2003 Server running SS2005 Standard Ed(64 bit). I haven't found any 64 bit OLE drivers with which to set up the linked servers. Not sure if reinstalling a 32 bit SS2000 Standard Ed (SP4) will work but we could go tha...more >>

I have a situation where a component needs to run a Stored Procedure but is not dependent upon it completing. Typically I would just make the request via ADO to be Asynchronous. However, the execution is terminated if I close the connection. Is there any way other than launching a new proce...more >>

I have a problem: I created a view on SQL. In a Miscrosoft Access database, I created a link to this view. I hope this view would only allow my user to see the records, but not update anything in the table. However, the view that linked through the Access database allows user to update or ...more >>

I've got a query that is using two hash JOINs. But I don't understand why because, between the three tables involved, there is a CLUSTERED INDEX SEEK, a CLUSTERED INDEX SCAN, and an INDEX SCAN. You can see this portion of the graphical plan at: http://www.medevolve.com/hashmatch.png I'm t...more >>

-------------------------------------------------------------------------------- UPDTE ReportInstance SET RolloffDate = DateAdd (Day, 2, GetDate()) WHERE ReportInstanceID = 1376540 -------------------------------------------------------------------------------- I got an error: "Line 2: I...more >>

I have the following SQL statement: ----------------------------------------------------------------------------------------------------------------------------------- IF NOT EXISTS ( SELECT * FROM syscolumns WHERE name = 'ManuallyResolved' ) BEGIN -- Add new columns ALTER TABLE Distribu...more >>

My problem is as follows. I wish to access IIS7 running on a remote machine. I have a SQL database that I am managing with SQL Server Management Studio Express and a C# solution in Visual Studio. In my solution I have two projects, one is an SQL Server project with some SqlStoredProcedures and ...more >>

Hello , We bought a brand new system to move our production databases to the new one. Our current production is running on Windows 2000 server and has SQL Server 2000. The new box is going to have Windows 2003 server OS on it. And we will be installing on SQL Server 2005 enterprise version...more >>

Hello everyone. I'm stuck with what must be a common sql challenge. I've got this single table that looks like this: Store, Original Store, Product, Sale type, Method, Amount There will be two product types (prodA and prodB) There will be two Sale Types (New and Recharge) There will...more >>

Hi, If I have something like Select * From Customers and i think there would be a share lock when the record is read. My question is the share lock of the record is released once it is read orthe record is locked until all the records are read? Thanks Ed...more >>

I have a table, very simple. It has a id field (identiy increament), field 1, field2, and field3. When I open the table, click on the next blank line, I enter data in field 1, field2, field3. Then I click on somewhere in the table, the id is increamented by one. I close the table, go back ...more >>

I have a newbie problem I am trying to solve. I have a field that is alpha numeric. I want to run a query that finds all the entries that can be converted to numeric and cast them as numbers. I have tried CASE ISNUMERIC(TESTFIELD) = 1 THEN CAST( TESTFIELD AS DECIMAL) but the problem i...more >>

Does anyone know why this is happening, or if it is a known bug? The following select statements is differ only in using "* 0.01" instead of "/ 100" in the calculation. Mathematically, these are identical. But, the results are different! select CONVERT(decimal(12, 2), 50 * 7907.91 * (...more >>

Hi All, I have a third party table in out database where there is a OWNER_LOGIN column which stores the LOGIN name. I could make a query to fetch the distinct OWNER_LOGIN by writting select distinct OWNER_LOGIN from <table>, but what I highly need is to get the same result set by writting th...more >>

We have an application that creates a new DB for each new project that gets added. We have a MasterDB that is used to keep track of these DB's. So as our application is used, and more projects are added by the application, our DB list looks something like MasterDB Project001 Project002...more >>

Hi, I have a table whose primary key data type is nvarchar. How can I retrieve the primary key of the last inserted row? Thank You ...more >>

Initially, i would like build a trigger at table Groups to disallow user to delete the system default data. create trigger [d_prevent_group] on [dbo].[GROUPS] after delete as declare @vargrp int select @vargrp = Groupid from groups If @vargrp = 1 begin RAISERROR('Unable to delete the ...more >>

Hello Which of the above methods is more performant for determining whether a field contains a particular sequence of characters? WHERE CHARINDEX('abc', FieldName) > 0 Or WHERE (FieldName LIKE '%abc%') I am guessing that CHARDINEX is better for this scenario but would welcome your...more >>

Hi, Looking in server profiler I see that when a connection is made the following options are set/unset: set quoted_identifier on set arithabort off set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on...more >>