I have a sql statement that returns the last four quarters of sales numbers for a given salesperson. What I would like to do is exclude the highest figure and the lowest figure then sum the two middle figures and divide by two. I was thinking a median function would help but couldn't find a...more >>

I have a request from a friend that I personally don't think is possible without a cursor loop: Lets say I have a function that returns a table: Similar to below - and it will only ever, no matter what, return one row. create function dbo.fn_henfruit(@a int, @b int) returns table as ...more >>

I'm looking to set the variable @BusinessDayFactor based on a select against a calendar table. The select will have one parameter @ReportDate. The logic is as follows: Given @ReportDate count the subsequent, contiguous days which are not business days if those non-business days are in the same...more >>

Is it possible to create a job that fires a .vbs script file? ...more >>

I am trying to use a IN statement Select * From ShowMenusFirst WHERE (Security In ((select Security from Users Where (Username='mkintner')))) ORDER BY Menus.Level; The results from the query select Security from Users Where (Username='mkintner') is 'Low','Med' I wanted the IN Statement ...more >>

the job names and step names are so encripted, they often look like 4DC18F43-DF2F-4F12-BAFB-58DD054EF0BA or exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='4ade808a-0199-4 is there an easy way to find out a report name the job is referring from sql side? thank ...more >>

Hi, I thought we can just do this insert...into statement to copy data from one table in another db but it gives me errors below. Both product tables have the same schema. insert into db1.dbo.product ( select * from db2.dbo.product ) Server: Msg 156, Level 15, State 1, Line 4 Incor...more >>

I have an int column for client ID (with unique contraint). Is there a good way to scramble the column? bitwise exclusive OR will keep the "neighbor" IDs still stay together....more >>

Hi, I'm at a standstill on this and hoping that someone can assist. I've looked at substring, trim, right, and left to no avail. I have a field called Jobname this field can contain different lengths of text. Here are some examples 3777914:lsfadmin:IVUSCOUPMANPROD:ivbld:U.D67829MVUSP002...more >>

Hello, I had asked this question before but I guess my description was not very clear. I am able to select all databases from master..sysdatabases, but I am not sure how to run queries against each so I have something like: Database Result database1 1 database2 2 In one table. I c...more >>

Hi all, I have added 70 records from another table into a table. When Selecting the count from Query Analyzer I get the correct new count. When right clicking on properties of the table I get the old count. Can someone explain this why? thanks gv ...more >>

Hello, I have a script which copies the xp_smtp dll to the bin folder of sql. In SQL 2000, I use the procedure or statement EXECUTE sp_MSget_setup_paths @SQLPath OUT to get the location (c:\program files...) to the bin folder. However, this doesn't appear to work in SQL 2005. Is there an equ...more >>

Hi, I'm getting the error: "Procedure 'PAFF_Activity_Calcs' expects parameter '@SubjectID', which was not supplied." In my stored procedure. I'm new to SQL and SQL syntax, so I was hoping someone could look at my procedure and walk me thru why it's wrong? This is it below: CREATE PROC...more >>

I have two tables - PartsShipped and PartsConsumed. Each table has three columns - Processdate (a date/time stamp), PartNumber, and SerialNumber. I need a simple query that will show me the 3 bits of info for all serial numbers that exist in the PartsShipped table, but not in the PartsConsum...more >>

I am trying to control the format of the text values into a 2000 sql server single column of a table. The coloumn is a varcchar(50) and the incoming values typically are as follows, “New Server” “new server” “Standard” “standart” “STANDARD” What I need is the first c...more >>

I am a DBA and have a development DB server (SQL Server 2005) where developers can send me a request, and I create them a blank database and administer it, perform backups etc. The users can add tables, views, data, etc. I want to automate a tool to track all the databases on the server. ...more >>

everyone, Is there is a INSTR built-in function at triggers? I tried to compare if a variable contains "FED". I tried If INSTR (@myvar, 'FED') > 0 but it said 'instr' is not a recognize functions. Please help!...more >>

Can you run a query that will return only results that have the data in a target column duplicated in another row? i.e. Duplicate address data Original table tblPeople: RecordID Last_name First_name Address ======== ============== ============= ================...more >>

Hi All, I'm currently trying to use LinRegSlope as follows: LinRegR2(lastperiods(60), [Measures].[Growth_Avg], ?????) I would like to regress the growth_avg measure against the following series (1,2,3,...,60). So, essentially I'm regressing against a time series from 1 to 60. I cannot s...more >>

I have a trigger that was copied over from sql2000 to a sql2005 box that was on the sysusers table. There were apparently 'problems' with the trigger, and not sure if it's even possible to actually create a trigger on the sysusers table anymore. Anyway, I can query the sysobjects and syscomments...more >>

I have a stored procedure that selects from a table, the details of the query or table aren't that important (it's quite simple). Right now, the columns it selects are specified explicitly. I'd like for the user to be able to specify which columns they want to select (There are 2 or 3 columns th...more >>

Hello, I'm trying to create a trigger that involves an ntext column and i'm running into problems. Upon modification to a row in table A, a trigger should determine if column A, which is an ntext column, was modified. If so, the trigger should take the new value for column A and insert it...more >>

When I create a backup job and schedule it, I get the below error. I can create the job and run it immediately fine. But if I try to schedule it, I get this error. This is Win 2003 Standard SP1 and SQL 2000 SP4. I also use Mixed Mode security model. I'm also creating and running logged in as ...more >>

I'm dealing with a legacy system (yecch) that I'm trying to search. The data I'm searching is a flattened representation of a tree. My problem is that my search results aren't being displayed in the correct order. Sorting order is controlled by settings stored in the database in two diffe...more >>

Hey everyone, I have a situation where I need to take one record and split it up over a range of integers that could be different for each record. The result would be one record for each integer in that range. Here is an example: The table contains START_NBR int END_NBR int SIZE_OF_RA...more >>

The notes field is type text in sql 2000. I need to append a line of text to it. This truncates at 8000: update tbl_main set notes = cast(notes as varchar) + ' This line is appended to the text field.' WHERE rcd_id = '01047' ...more >>

I want to recover data in a table on SQL 2000 server. This database has 10 files (.MDF, NDF, and LDF). I know which .NDF file contains the data of the table. I made detach the database. Can I use one old backed up .NDF file replace the current .NDF file, and together with other files to atta...more >>

Hi all, Just a quick question...can anyone tell me if SQL allows you SELECT...FROM (sub query) WHERE...etc? i.e is this query possible without first inserting the sub query data into a temp table? SELECT place1, SUM([value]) FROM ( SELECT place1, place2, [value] FROM flow WHERE prodid = 1 ...more >>

Do we know, is it possible to copy the results passed from a select into a variable (I think similar in the way one can pass a resultset back from a UFN), so one doesn't have to keep on querying the table to get the results when required, this variable used as an expression in a nested select ...more >>

Hello, I am looking to group my query by month and having some problems. My Query: SELECT CONVERT(varchar, DateStamp, 110) AS DateAdded, COUNT(CONVERT(varchar, DateStamp, 110)) AS [#_of_Articles] FROM InstantKB_Articles GROUP BY CONVERT(varchar, DateStamp, 110), ORDER BY dat...more >>

Hi all, I have a large table from which I need to fetch data according to a given filter conditions. But I will only need to fetch a set of records (Eg : To display the first 200 records in the table.). Still I would need the total no. of records which will satisfy the filter to display a t...more >>

I am getting the following sub-query error msg, Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. I understand why I am getting the msg, just not how to resolve the issue. The SR_Master_D...more >>

Hi, can anyone tell me how I can tell if a machine has applied Daylight Time or not? I need to find this out in a stored proc in both SQLServer2000 and 2005 using T-SQL. TIA John...more >>

Hello, I need to write a script that will loop through data within a MS SQL database and print to a specific printer. I'm totally green when it comes to VBScript, so I'm not sure if this is possible and if so, what commands to research. Can someone point me in the right direction? Thanks,...more >>

Hi all, when I have the script: declare @week int declare @year int set @week = 35 set @year = 2006 how can I obtain the monday of that week? result: 14/08/2006? Thanks a lot. ...more >>

Hi All, I have a string 'ABC 12345 678910 XXXX '. I want to insert it into the test.txt file using bcp command. How do I do that. Thanks for your help. ...more >>

today when I come to work, I noticed someone had modified and deleted few columns n a table. Is there any way I can find out when and who modified and deleted columns? is there any scripts i can run? thanks Farrukh...more >>

Hi, I have the following data from Excel spreadsheet. LST125A LST075A LST040A PBL125A LST125A 60 240 240 360 LST075A 240 60 240 360 LST040A 240 240 60 360 PBL125A 360 360 360 60 I need to extract t...more >>

Hi guys, I'm new to SQL programming and I want to know if there's a way to retrieve user in a database that as space in is name. I'm able to retrieve user with sp_helpuser but if there's a space in the name of the database I get and error message. Is there a way to get thru this ? ...more >>

When I start a bulk insert from a network share locally on the server I have no problem. When I start the bulk insert (is inside a stored proc) from a remote computer I get the following error message: Msg 4861, Level 16, State 1, Line 1 Cannot bulk load because the file "\\otherserver\shar...more >>

DECLARE @ReceiptItems XML SET @ReceiptItems = ' <Root> <Table> <PaymentDate>1/1/2000</PaymentDate> <PaidBy>SRamesh</PaidBy> </Table> <Table> <PaymentDate>1/1/2002</PaymentDate> <PaidBy>subramaniayn</PaidBy> ...more >>

Hi i want to randomly pull out a load of records. I'm quite a newbie to SQL and was hoping someone could help. I found some code and have tried to hack it together for our SQL database. I want to pull out around 1000 randomly selected records. Can anyone please help modify my code so that it wor...more >>

I am trying to extract element values from an xml document but having problems as the document contain namespaces. This is my code: declare @hDoc int, @XmlRequest XML set @XmlRequest = '<?xml version="1.0" encoding="utf-8"?> <dt:AddTestRequest xmlns:dt="urn:org.uk.telcob2b/tML/A...more >>

I've written quite a complex query and i've been trying to tune certain parts of it by adding and dropping indexes. In one part of this query it was joining to a query (made up of columns from two tables called consumerbase and consumerextensionbase) and showing the estimated execution plan i...more >>

Hi, I'm developing a courier appln which contains the customer rates different for each customer. i need to fetch the customer rates from the db. Each and every customer have different rates for the different countries, cities. I can't categarize into group bcos of variation in prices with cu...more >>

Am I overlooking something or are only full and log backups available in maintenance plans?...more >>

I am getting a time out when I execute a stored procedure, I would like to know where it is timing out (it normally took < 30 sec to run), but no print commands work so I am at a loss where the issue is being raised. Where is that supposed to be that I can find out where the issue is causing ...more >>

Hi, I am not sure how to get this write. I need to evaluate the @Direction param and if it null then add the following condition to the SELECT statment: AND T1.[Id] IN (SELECT [Id] FROM Deals) ----------- DECLARE @FromDate AS DATETIME, @ToDate AS DATETIME, @Direction bit; SELECT @FromD...more >>

HI all, I have an insert trigger that inserts additional rows based on the row that the user inserts I also have a bridging table that gives me a m:m relationship with another table. After the user insert rows into the policysection_division table, the trigger then inserts multiple rows(dep...more >>

Platform: Windows 2003 R2 64 bit 32 GB RAM Sql Server 2005 Enterprise 64 bit with SP1 and all fixes applied Executing Parse in Sql Server 2005 Management Studio I get the following error: ".Net SqlClient Data Provider: Msg 0, Level 11, State 0, Line 0 A severe error occurred...more >>

Hi all, Does anybody know of a better place other than the MS newsgroup ms.public.sqlserver.olap to post MDX questions? I have what I would believe in a SQL world, a very simple question but my MDX sucks! Or if someone here has a descent knowledge of MDX, could I post my question here a...more >>