Hi Since I have never had a good search methods I am looking for some reading material/source code to know what is achievable relatively easily Thank you for your suggestions, Samuel ...more >>

i have a really simple (heh select ID,surname,postcode from table) query that selects from a table 3 columns ID (key field - auto increment) Surname Postcode for example it returns 1 smith blah 2 smith blah 3 smith blah 4 jones test 5 arkwright bl00 5 arkwright bl00 6 smythe w000 ...more >>

Here is my query SELECT TOP 1000 Team, Segment, Associate, SUM([Adjusted Weight]) AS [SumOfAdjusted Weight], SUM(ExtSatWt) AS SumOfExtSatWt, SUM(VerSatWt) AS SumOfVerSatWt, COUNT([Surv Strt Tm]) AS [CountOfSurv Strt Tm], [Date] FROM dbo.CSI WHERE ([Dat...more >>

Hi, is there a way to store the result of READTEXT function to a variable? Thanks...more >>

I am using SQL 2005. Could you please give me the most basic Instead Of Insert Trigger for updating a view composed of two Tables. F0r example: ProductTable Columns are ProductID (int, key) ProductName (varchar) ProductAttribute Columns are Attribute1, Attribute2, ProductID. The View is V...more >>

I have a new Windows 2003 64-bit server on which I'm running a 32-bit SQL Server 2000. I backed up the database on the old (Windows 2000 SQL Server 7) server, copied the backup file across the network, and restored it on the new server. I'm finding in some cases that I'm getting very, very p...more >>

Hi can anyone help me out here, i have a table which I am importing data into usin SQL 2000 DTS,it has a number of columns but i am only interested in the two shown below. Table name as400DocData PageNum OrderNumber 1 123456 2 123456 1 654321 2 65...more >>

Here is the situvation. I can get BO_ID Field Value based on ROW_ID but I could not get the same record if I give the BO_ID Value. Please hava a look at the following query and other details. Can someone explain why and how it happens? Is it due to QUOTED_IDENTIFIER ON/OFF Setting. Let me ...more >>

I have a query that generates the dataset below, based on the year being filtered I get the sum of an amount Group By the type. What I would like to do is use the exact qry using a differnet date, to generate a third column called Prior12Mnths. How would I use my qry to accomplish this task. I a...more >>

If they (CTE) were parameterized, I would stop working user functions for use in only one place. /* * CTE, I have a dream... */ With TopRatedEmployees ( @DepartmentId int ) RETURNS TABLE AS ( SELECT TOP 10 EmployeeId, EmployeeName, Rank FROM Employ...more >>

Approximately once a week for the past 3 weeks we have had some issues with the custome web content management system sql database that have caused signification problems with the editorial tool and/or live site rendering. The primary symptoms have been extreme sluggishness in the tool, the pu...more >>

I'm trying to create a UDF that reutrns a table and i'm attempting to ise conditional logic. So far i'm missing something. It wouldn't be the first time. Anyway, semi psudo code is below: something like this RETURN IF @mode = 1 THEN SELECT X FROM Y WHERE x > 7 ELSE IF @mode = 2 THEN SELEC...more >>

I am trying to do a bulk insert with the following statement: BULK INSERT dbo.Rand_By_Day from 'C:\Test\Randfinal.txt' I get the following error: Msg 4861, Level 16, State 1, Line 3 Cannot bulk load because the file "C:\Test\Randfinal.txt" could not be opened. Operating system error code...more >>

All, Is it possible to get something like a diagram of the SP relations? I'm working with one DB and the business logic is done so that the SP can execute another SP inside itself, the next SP can call another one, etc. So that we can get 3-4 different sub-layers inside one SP. If I need to...more >>

Hi - Sorry for this post (as I'm sure it's been answered a million times by now), but I wasn't able to find a real clear example of what I am trying to do. I have a customer who has some data that I am wanting to import in through a DTS package. Problem is that there are over 500 records i...more >>

Can it ? is it this simple? Or is it supposed to > ? DELETE FROM LOGRECS WHERE datediff(d,getdate(), time)<=30...more >>

Hello I have a table called Logs which has 4 columns IP varchar(30), Users varchar(30), Logdate (smalldatetime), Logtime varchar(30) Not best table design but its a quick project. I need to find the most recent logdate and time for each user. Example data IP Users ...more >>

Normally I bracket my stored procedure code with 'SET NOCOUNT ON' and 'SET NOCOUNT OFF' statements. Do I actually have to explicitly add the 'SET NOCOUNT OFF' statement at the end of the Stored procedure or does SQL Server automatically set it to OFF once the stored procedure is finished exec...more >>

Hi, I have a date column where the application users puposely enter a date way in the future as part of their business rule. For instance, entering the year 2033 if the given value for this date column is unknown. I need to programmatically retrieve this date and represent those out-of-w...more >>

Can I use a CTE inside a user defined function? If so, could you lead me to an example. Thanks, ji ...more >>

Greetings, We're currently starting to utilize updategrams with SQLXML 4.0 and SQL 2005. Part of the research, at this point, is utilizing optimistic concurrency in the updategram using record timestamps. This seems pretty straight forward, however we're having a problem that's causing me...more >>

Help!!! I'm in a time crunch and need to calculate a weighted average. Example: 85% of players will win $10.00, 5% will win $100. I have a table that is storing the counter information. I just drawing a blank!!! Any help is appreciated!!!...more >>

What happens when you run a SP inside of another SP? My case: At the beginning of the SP I want to call another SP. If I do this will the called SP finish running before proceeding to run the rest of the code? Or do they run simultaneously? Thanks. ...more >>

I have data on MS SQL Server that have over 60 columns, I would like to select 30 of these columns that may or may not contain NULL and I don't want to write out all 30 columns and check for IN NOT NULL. Does anyone know how to do that? ...more >>

Considering the following: CREATE TABLE CustomerTypes ( PKCustomerType INT IDENTITY (1,1) NOT NULL, CustomerTypeDesc VARCHAR(30) ) GO INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Retail') INSERT CustomerTypes (CustomerTypeDesc) VALUES ('Wholesale') INSERT CustomerTyp...more >>

Hi, I have a table with following raws. account Channel abc1 PT abc1 ST abc1 ZZ xyz2 PT xyz2 ST xyz2 ZZ abc2 PT abc2 ST abc2 ZZ xyz3 PT xyz3 ST xyz3 ZZ ...more >>

Hi all, Our production server had a raid failure, which we fixed overnight. This is where the problem started, at least i think. Now all of a sudden(after the server was restarted) i am getting following error, when one of the stored procedure gets executed (I have more then 200 Stored Proc...more >>

Using MS SQL 2000 I have a stored procedure that processes an XML file generated from an Audit program. The XML looks somewhat like this: <ComputerScan> <scanheader> <ScanDate>somedate&time</ScanDate> <UniqueID>MAC address</UniqueID> </scanheader> <computer> <ComputerName>Rya...more >>

Using MS SQL 2000 I will try and post all relevant information. -------- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset GO if exists ...more >>

Nob Hill Software, creator of several SQL Server tools, now have their own SQL Server schema comparison tool. It is Superior to other tools on the market in several ways; You can download your own copy at : http://www.nobhillsoft.com/NHDBCompare.aspx. Since this tool is new, we are mainly look...more >>

Would like to take your valuable views on ,What’s the best strategy for Database back up and files back up:- I have few options:- • Using Database Maintenance plan • Using State Store • On SQL Server, using Differential on daily & Complete back on weekend.. • Other options :- ...more >>

Hi there! I'm having some problems with a query considering it's performance. I want to make a view of saleslines (table 1) joined with a purchline (table 2). The purchline should show the last purchamount of an item, having the latest date before the salesdate. When running the query I ca...more >>

For reasons i wont go into i need to have an identity field when returning a select statement ie Select * from Table1 returnn Field1 field2 etc aa aa aa bb bb bb and i want Field1 field2 etc IDENT aa aa aa ...more >>

We have a Sql Server Manageent Studio Solution, that on loading gives the following error: [ Source Control ] There appears to be a discrepancy between the solution's source control information about some project(s) and the information in the project file(s). To resolve this discrepency...more >>

Ladies / Gentlemen I would like to use the sp_helpdevice with the sp_addumpdevice to check to see if the device is already added before I try to add it again. Set @Return_Code = ( sp_helpdevice 'myDataBase' ) If @Return_Code = 0 then EXEC sp_addumpdev...more >>

Hi all, How can I select the contents of a SP to a text file Thanks Robert ...more >>

I want to update the DateTime field of a table to contain only Date information. The query below works fine but I wonder if there are better solutions to this? Example: Old values: [id] [inDate] ID1 2006-06-27 06:03:23.230 ID2 2006-06-28 09:03:23.230 New values: [id] [inDate...more >>

hi, i have tables like sells, byus, rents, credits they all have a FK to table partners - partner_guid how do i get all sells, buys ... for a given partner w/ 1 query? i`ve been thinking about UNION but it will require(will it ?) me to write all where and join clauses 4 times TIA...more >>

Hi all. It looks like I need READ rights on the involved tables using Exec (SQL) within a prosedure instead of SELECT TableName and so on.It is not enough to have EXEC rights on the prosedure itself. Is there any way around this or do I need go grant read rights on all the tables in the SQL s...more >>

Hi I've been told that I can use the following SQL expressions to find out the current SQL being executed under a certain SPID, but for some reason I cannot get this to work. Is there something wrong, in what I have written? --************************************* DECLARE @Handle binary(...more >>

Hello, I have a numeric field with 4 decimal places. In the select query i want to eliminate trailing zeros. DB Value Result 1.1234 1.1234 1.1230 1.123 1.1200 1.12 1.1000 1.1 1.0000 1 Thanks in advance. M. Subbaiah ...more >>