When I execute the query "Select @@VERSION as 'SQL Server Version'" from SQL Server Management Studio Express, I get '2005 - 9.00......." returned as the version. When I execute the same query from a VB 6 program, I get "Microsoft SQL Server 2000 - 8.00.760 (Intel X86)" returned.
What is odd is the VB 6 program had been returning '2005' within the last two weeks. I have not uninstalled or re-installed 2005 in my environment.
I also executed the following at the command prompt and recieved SQL 2000:
"C:Documents and SettingsAdministrator>sqlcmd -S MARKB-LTXP -U SA -P medw.1 -q "select @@version"
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------------------------------------ Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)"
When I execute 'exec master..xp_msver' as a query, 2005 is returned but when done from my VB 6 program I get 2000 returned...
here is my business/data object for some reason I only get the first character back, say value is Charlie, I only get C public string GetUserName(long UserId) { try { string userName;
DiscussionDB data = new DiscussionDB(); List paramlist = new List(); paramlist.Add(data.CreateParameter("@UserId", UserId)); paramlist.Add(data.CreateParameter("@UserName", "", ParameterDirection.Output, DbType.String, 20)); data.ExecuteNonQuery("dbo.Discussion_User_Name", ref paramlist); userName = paramlist[1].Value.ToString();
return userName; } catch { throw; } }
ALTER PROCEDURE [dbo].[Discussion_User_Name] @UserId bigint, @UserName varchar(20) output AS SET NOCOUNT ON
SET @UserName = (Select [Name] from Discussion_Member WHERE UserID = @UserID)
if (@UserName is null or @UserName = '') BEGIN SET @UserName = (Select UserName from Membership_User WHERE UserID = @UserID) END
(...) insert into #tempB (colA,colB,colC) select colA,colB,colC from #tempA order by colB,colA,colC
select * from #tempB
While using the reserved memory for the SQL Server, the last 'select' works pretty well. After the memory swapping starts, the 'select' returns data in incorrect order.
Any Ideas? Thank you for all support than you can give me.
Hi, I've got what is probably a simple question, but for the life of me I cannot figure it out and it is starting to do my head in . I have the following query:- Select e.intEmployeeID AS ID, e.txtFirstName AS [First Name], e.txtLastName As [Last Name], e.txtMobile As [Mobile], i.txtWorkEmail as from EMPLOYEE AS e INNER JOIN EMPLOYEE_INFORMATION AS i ON i.intEmployeeID = e.intEmployeeID WHERE (((e.blnActive = -1) AND (e.txtFirstName Like '%b%')) OR ((e.blnActive = -1) AND (e.txtLastName Like '%b%')) ) ORDER By e.txtFirstName In my database, the field blnActive is a BIT field. When I try to run this query, I get no results returned. If I remove the (e.blnActive = -1) WHERE statement segment, I get the correct results. The field blnActive contains -1 for every record but I cannot work out why it will not return these records .... aaarrrrggghhhh Thanks to those who feel my pain !!
I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.
My query would look something like this:
Select leadershipName From Leadership
Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:
Executive Board Members, Delegates, Grievance Chairs, and Negotiators
My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?
Hi all - i'm trying to put together my first .Net web page (have switched from Dreamweaver to VWD - VWD keeps swapping my tab-indents for spaces, and none of the options stop it!).Here's a table that i'm trying to query: ItemID | ReviewRating | ReviewRatingOutOfAs i'm sure you've guessed, it's a reviews table, where there can be several records with the same ItemID and different (or the same) ReviewRating and ReviewRatingOutOf's. As the reviews are collected from lots of sources, the ReviewRatingOutOf will change (one review might be 3/5, while the next, for the same ItemID, could be 8/10, etc). Now, what i'm trying to do is return a list of ItemID's ordered by their RATIO (which is the sum of each ItemID's ReviewRating's divided by the sum of each ItemID's ReviewRatingsOutOf's - in other words, average score). My first guess was this:"SELECT DISTINCT ItemID FROM Reviews ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" - unfortunately that doesn't work (problems with the SUM aggregate functions, and overflow errors, whatever they are). Now, this string works: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)" - right now, that just adds up the ReviewRatings, so an item with 10 reviews that only got awarded 1/5, 1/10, 1/8, etc (all 1's, therefore achieving a combined ReviewRating of 10 out of a very much higher ReviewRatingOutOf), would appear higher than an item with 1 review that got 5/5. Making the string into this: "SELECT ItemID FROM Reviews GROUP BY ItemID ORDER BY SUM(ReviewRating)/SUM(ReviewRatingOutOf)" (which is what I need), unfortunately gives me errors...Anyone have any ideas? Is there possibly a way to simply read all the distinct ItemID's with SQL, then get the two SUM's for each ItemID, then calculate the ratio of the two SUM's, and stick the ItemID's and the ratio into some sort of array, and have C# order the array for me, based on the ratio? I'd appreciate an example of that if possible, as i'm a complete C# beginner :-)Thanks in advance!
I am doing some SELECT queries on my database through ASP, but for example, I only want to return the 50 most recent entries that match the criteria. Is there any easy way to limit the number of results returned?
I am getting the following results from my query that contains a subquery, but I don't understand why the values in the [Total Volume M_Active_Previous] are being repeated with the same value. I should be getting different values returned for each row like in the [Total Volume M_Active] column.
Why the values are all the same and how I can fix this?
Hi, Can we validate the returned order of fields from a stored procedure? Infact, i am taking a query as user input and extracts the results based on the query but for that order of fields specified in a query is important. Can i check the order after the query is run i.e if this is entered "select field1,field2,field3 from table" then i need to check the order of the resultset generated. I can't check the query before the resultset is generated because a user can enter bunch of queries. Any way will work, tsql or .net app. Thanks,
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?
this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
I am trying to pull results from an SQL Server DB into an dataset where a particular field (SMALLDATETIME) is within a particular date range. The code I was using worked fine when the database was in Access. I have made several changes already but am still getting 0 results returned when there is data that should be returned.
I was using in Access: Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>=#" & startDate & "# AND myDateField<=#" & stopDate & "# ORDER BY ID" I have changed this for SQL Server to: Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>='01/01/2003 00:00:01' AND myDateField<='01/01/2004 23:59:59' ORDER BY ID" But I am always returned 0 results even if the date range should return plenty of data. I have also tried using the BETWEEN structure with the same result.
Is there a particular format for the date I am comparing with? Am I missing something else in my query?
The connection / permissions and everything else are correct as I can read and write data to the database in numerous other pages. It is just this date comparison that is not working.
Many thanks for any help or comments you can provide.
When I set the SecondOperand to /* the output is a concatenated text string of DeviceID and UserID values. I'm trying to get just the DeviceID but perhaps my understanding of XPath syntax is wrong. I've tried setting the SecondOperand to the following:
I have two tables A and B, A has 8000 and B has 8122 records. I want to see what records are missing. I tried EXCEPT and it returned zero rows. I used where non exists also still no records.
When clicking on the header for the Date Created column in EM (SQL Server 2000) , in the tables list for a database, I noticed that the tables don't get sorted by created date at all - looks like random order, without looking too close.
Is this is a bug (probably not??) or some kind of collation-related problem?
Stored Procedure ProblemThis is probably a simple problem but i would appreciate some help.I have a stored procedure that takes the order date time plus other information fromvarious tables but the information is being returned double:ie 4 rows are being returned instead of two. Can anyone see where i am going wrong?Many thanksMartinThis is the stored procedureALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct OD.DateCreated, O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNThese are the results that are returnedDateCreated OrderID UserID OrderTotal Sent Delivered Paid CustomerName ----------------------- ----------- ------------------------------------ ---------------- ------ --------- ------ --------------- 18/10/2006 14:49:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 14:49:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 18/10/2006 15:05:00 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(4 row(s) returned)If I leave OD.DateCreated ie use ALTER PROCEDURE dbo.SP_RetrieveOrdersASSELECT distinct O.OrderID,O.UserID,O.OrderTotal,O.Sent,O.Delivered,O.Paid,C.CustomerNameFROM Orders As O,Customers As C,OrderDetails as ODWHERE O.UserID=C.UserID And O.OrderTotal >0 RETURNthen there is no problem. I get:41 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 500 <NULL> <NULL> <NULL> bill 42 7A2E2B9B-57FA-4329-B4BB-D7ED965AA183 590 <NULL> <NULL> <NULL> bill No rows affected.(2 row(s) returned)
Hi, I need to write a select query that will run all returned results through a separate stored procedure before returning them to me. Something like.... SELECT TOP 10 userid,url,first name FROM USERS ORDER by NEWID() ......and run all the selected userids through a stored procedure like..... CREATE PROCEDURE AddUserToLog (@UserID int ) AS INSERT INTO SelectedUsers (UserID,SelectedOn) VALUES (@UserID,getdate()) Can anyone help me to get these working togethsr in the same qurey?
My OLE DB Source is getting data from the following column types:
ID varchar(50), Name varchar(100), Date datetime, Currency char(3), Cost numeric(30,10)
My OLE DB Source outputs my information in the following order when I click Preview:
ID Name Date Currency Cost
When I connect the OLE DB Sorce to a Flat File Destination, it comes out in the wrong order.When I examine the "line" between them (Data Flow Path Editor) I get:
Currency DT_STR Length: 3
ID DT_STR Lenght: 50
Name DT_STR Lenght: 100
Date DT_DBTIMESTAMP
Cost DT_NUMERIC
What is the easiest way for me to change this so the Flat File Destination will output my data in the same order as the OLE DB Source:
I have a relatively simple SQL Server 2000 database which I amquerying from a VB.NET application. The query is very simple, such asSELECT * FROM tblSystems. Let's say there are 10 records in the tabletblSystems. I expect to get 10 records from this query, and sometimesdo. But for some reason, I start getting 0 records from my app. If Irun the same query in SQL Server's Query Analyzer, it works fine. IfI reboot my machine, then it seems to work fine for a while, thenstarts giving me bad results again. I am running my app on the samemachine as SQL Server, although I can connect to my database remotelythrough my app, and I get the same results either way. Anyone seenanything like this? Was this fixed in a service pack (I'm using SP1,and don't want to install a newer one unless it's a known fix)?Thanks,Andrew
I'm reading values from a named range within an Excel spreadsheet using the Excel ODBC driver. If I ask for all columns within a range, using e.g. select * from 'named range', does the driver ensure that the returned rowset has the same column and row ordering as in the spreadsheet? In other words, if a named range on a spreadsheet is the block of cells:
name age
richard 54
jemima 27
I want to make sure that my returned rowset is not going to be:
age name
jemima 27
richard 54
I know that proper databases do not guarantee the order of returned values (unless you specify it) but since Excel is a fixed view of data I was hoping that a returned rowset of values would retain their spreadsheet ordering.
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
Hi, I was wondering if any SQL Server gurus out there could help me...I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g. <META NAME="MS.LOCALE" CONTENT="ES"> and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)
However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g. DECLARE @SearchWord nvarchar(256) SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources. SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)
I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column.
Any pointers / suggestions would be greatly appreciated. Cheers, Gavin.
We are experiencing a problem in SQL Server 2005 Standard Edition (on x86 & x64, RTM & SP1 CTP1). The problem is we have a view which does something like "CREATE VIEW myView;SELECT * FROM MyTable WHERE ISNumeric(MyVal)=1" when you do "SELECT * FROM myView" you see a dataset which only contains numeric values.
However it's clear that if you do "SELECT * FROM myView WHERE MyVal>5" that it is evaluating the >5 before the IsNumeric function (I assume as > is less costly than IsNumeric and thus it is more efficient this way). This didn't happen in Sql Server 2000 & 7.0.
My concern here is that how can you trust views if when you put evaluations on them they're working against a different dataset to that which you view if you do SELECT * ? I am currently working with a workaround which is to simply put TOP in the sub-queries to force the execution order to that which I've defined. However this is nasty as I can't do TOP 100% as it gets optimised out and so instead I have to do TOP 999999999 or similar.
However my biggest concern by far is that even in "SQL Server 2000 (80)" compatibility mode the behaviour is not consistent wtih SS2000.
CREATE TABLE #Problem (idkey int IDENTITY(1,1), numinastr varchar(25))
INSERT INTO #Problem (numinastr) values ('1') INSERT INTO #Problem (numinastr) values ('10') INSERT INTO #Problem (numinastr) values ('25') INSERT INTO #Problem (numinastr) values ('40') INSERT INTO #Problem (numinastr) values ('>500') INSERT INTO #Problem (numinastr) values ('600') INSERT INTO #Problem (numinastr) values ('1000') INSERT INTO #Problem (numinastr) values ('error!')
-- Note Lack of any non-numeric rows SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1
-- This Command executes correctly SELECT numinastr FROM #Problem WHERE ISNUMERIC(numinastr)=1 AND numinastr>15
--This one however is parsed incorrectly, with >15 being evalutated before ISNumeric SELECT * from ( SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1 ) a where numinastr>15
-- Creating a view of SELECT * FROM #Problem WHERE ISNUMERIC(numinastr)=1 and -- then querying that also gives the same error
DROP TABLE #Problem
I have been told (by an MVP) that you can't assume a specific execution order for queries. Do any DBA's out there really think this acceptable? I consider this a bug. If I put a query in as a sub-query or view, or if I bracket my where statement in such a way I expect it to respect what I've told it!
Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database. I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple... One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')' EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed). Does somebody know how to make what i want?? I prefer to avoid using temporary tables. Thanks!
I have the following query and I am wanting to get the results to be in order. Right now, it shows me the results by date, but the dates are out of order. How can I get it to give me the results by date in date order???
SELECT DISTINCT MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59')) AND DATEPART(weekday, CallDate) in (2,3,4,5,6)) Group By MDN, dateadd(day, datediff(day, 0, CallDate), 0) UNION SELECT DISTINCT MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007' and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59')) AND DATEPART(weekday, CallDate) in (2,3,4,5,6)) Group By MDN, dateadd(day, datediff(day, 0, CallDate), 0)
Hello EveryBody, I have a little problem in Sql Statement .. When I am adding a Where condtion to my sql statement ..I got an error shown in the pic blew :I am waiting for your solution Best regards,
