SQL Server 2005 - SELECT Query
Oct 18, 2006
Hi
I currently have a select query with "Description = 'input from user
here'" which basically returns the associated row containing the
Description field exactly as typed by the user, however, would it be
possible to, if not found, return the closest match? I am using
Microsoft Visual Studio 2005 with C# as language and Microsoft SQL
Server 2005 as database.
Regards,
Lionel Pinkhard
---
avast! Antivirus: Outbound message clean.
Virus Database (VPS): 0642-0, 2006/10/17
Tested on: 2006/10/18 12:46:07 PM
avast! - copyright (c) 1988-2006 ALWIL Software.
http://www.avast.com
View 4 Replies
ADVERTISEMENT
May 28, 2008
Hi Friends,
I have created the procedure in sql server 2005 for retriving email addresses from table based on date_expiry and concatinating all email addresses in to @tolist as shown below
Declare @tolist varchar(8000)
set @tolist = ''SELECT @tolist = @tolist + ';' + COALESCE(email, '')
FROM awc_register
WHERE DATEDIFF(day, date_expiry, GETDATE())='3'print @tolist
set @tolist = substring(@tolist, 2, len(@tolist))
and I passed this @tolist to another procedure which should send mails for email addresses present in @tolist variable.
Problem is I need to send the mail to each email address separatley.( not bulkly)
Thanks,
View 1 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Jun 26, 2015
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
View 4 Replies
View Related
Nov 9, 2006
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
View 1 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
May 2, 2006
Hi,
Recently the powers-that-be migrated the largest databases from one server to another, more powerful server while keeping some support data on the original server. My problem: I need to run queries on tables spanning both database servers. Unfortunately, I can't find any documentation on how to do this. Does anyone have any ideas?
Thanks!
View 1 Replies
View Related
Mar 1, 2008
hi,i have three database servers with heterogeneous databases, and i have a sql server 2005 that should has a table that will be filled with records from the three servers every time period, so what would be the best technique to create this table with the scripts ????i used to use linked server + sql server agent jobs but usually for one linked server only, but this time i am afraid of the performance as there will be three linked servers, so i need ur suggestions.
View 2 Replies
View Related
Sep 30, 2005
I am trying to install SQL Server Sep 2005 CTP - ENTERPRISE, there are
View 1 Replies
View Related
Sep 6, 2006
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
View 4 Replies
View Related
Mar 29, 2007
The SQL statement below works without the WHERE statement but as soon as i try to use the where it stops working, I think it has something to do with the HAVING statement in the vwMultiAE2 view but I am not sure.It did the same thing when I had it in one view. Anyone know what is going on here?SELECT Ch, Reg, FMM, AE, Acc, Totals FROM vwMultiAE2 WHERE (g1ID = @g1 or @g1 = 'All') AND (g2ID = @g2 or @g2 = 'All') AND (g3ID = @g3 or @g3 = 'All') AND (AEID = @g4 or @g4 = 'All')
vwMultiAE2 >
SELECT TOP 100 PERCENT
Ch,
Reg,
FMM,
AE,
Acc,
Totals,
g1ID,
g2ID,
g3ID,
AEID
FROM
vwMultAE
WHERE
Account_ID IN (SELECT Account_ID FROM vwMultAE GROUP BY g2ID, Account_ID HAVING ( COUNT(Account_ID) > 1 ))
ORDER BY
g2ID, Account_ID
View 5 Replies
View Related
Dec 16, 2007
Hi all,
I have to write a select query which need some logic to be implemented.
Query is like
select name,number,active,
if active ="true" then
select @days=days from tbl_shdsheet
else
@days=''
end
from tbl_emp
In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet
So how can i write queery for this.
View 3 Replies
View Related
Jan 23, 2015
create table [where] ([where] char(5))
go
insert into [where] ([where]) values ('where')
go
select [where] from [where] where [where] = 'where'
go
drop table [where]
go
View 3 Replies
View Related
Nov 6, 2006
I just want a simple datareader, that i can read the value returned from a select statement executed on a SQL server 2005 db. The code below should work in, but email[calc]= rdr[0].ToString(); when i want to read some data a get a exception saying:System.InvalidOperationException was unhandled by user code Message="Invalid attempt to read when no data is present." Source="System.Data" StackTrace: at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i) at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i) at _Default.Login_Click(Object sender, EventArgs e) in d:My DocumentsVisual Studio 2005WebSitesWebSite1Default.aspx.cs:line 47 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) If anybody could advise me where my stupid mistake is then i would highly appreciate it! SqlConnection conn = new SqlConnection(getConnection()); SqlDataReader rdr = null; SqlCommand cmd = new SqlCommand(); cmd.CommandText = "SELECT * FROM Customer"; cmd.CommandType = CommandType.Text; cmd.Connection = conn; try { conn.Open(); rdr = cmd.ExecuteReader(); int calc = 0; Boolean login = false; string[] email = new string[100]; object[] password = new object[100]; while (rdr.HasRows) // or rdr.Read(); { rdr.Read(); email[calc]= rdr[0].ToString(); password[calc] = rdr["Password"].ToString(); if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc])) { login = true; } calc++; } } finally { rdr.Close(); } thanks....
View 1 Replies
View Related
Nov 17, 2005
We have a web app that accesses Sql Server 2000 using windows authentication. We recently installed SQL Server 2005 on a new server and are in the process of porting the data from SS2000 over to 2005. I expected some problems, but I've run into something that seems like it should be relatively simple to solve, yet I have been banging my head against the wall for a couple of days. After changing the connection string in web.config of the web app to point to the new SQL Server 2005 database, I keep getting ... SELECT permission denied on object 'tUser', database 'GDoc', schema 'dbo'. The problem is, when I go into Management Studio and check the permissions, the user I'm logged in as DOES have select permissions on that database. What am I not doing? Am I forgetting something simple?
View 4 Replies
View Related
Mar 7, 2007
Dear All,
i am making small web application using asp.net, C# ,sql2000.
i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.that is how to make select query to access view in other server Please help
thanks
View 1 Replies
View Related
May 22, 2015
using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.
select id, city,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,
'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),
'JRK_Saldae', 'Bougie')))
New_city_name
from towns
View 3 Replies
View Related
Aug 27, 2015
I have a table (ScriptTable) which holds a groupID Nvarchar(10) ,SQLStatement Nvarchar (150)
Table Fields =
GroupID SQLStatement
1234 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'AB123'
9876 Select CUSTNO, CUSTNAME,CUSTADDRESS from custtable where customerNo = 'XY*'
What I need is to take each select statement in turn and add the data into a temp table. I can use any method but it needs to be the most efficient. There can also be a varying number of select statements to run through each time my job is run.
View 6 Replies
View Related
Dec 4, 2007
Can anyone tell me how to do this in sql server?I am currently doing this query in oracle:select table1.col1,table1.col2,table2.col3,table4.col4where table1.col1 = table2.col3 andtable2.col3 = table4.col5 and(table1.col1,table1.col2) not inselect table2.col4,table2.col5 from table2it is the where two column values from any row are not found in anyrow in table2 part that I can't figure out.thanksJeff
View 10 Replies
View Related
May 9, 2015
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly..Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
FROM myTable
[code]....
View 5 Replies
View Related
Sep 6, 2015
I have a table like the following (with much more data, but the concept is the same) with Dates and Actions for People and a column called Action with beginning Dates and end dates.
(I attached a picture because I could not figure out how to Format it)
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
I need the query to be like this. The idea is to have the query grab the next 'END' not all Ends, which my attempts have done i.e. I get not just the closest end to the begin date, but ALL Ends with the same Person.
I Need it to look like this:
begin Date end Date Name
begin 2014-10-15 end 2014-10-31 phil
begin 2014-09-18 end 2014-09-30 phil
begin 2014-08-21 end 2014-08-23 John
There can be different People so the query Needs to return the beginning and end rows for the Person in sequential order.I can't figure out how to select only the 'next' end. My query always gets 'end' values that have a 'begin'. I
View 8 Replies
View Related
Sep 29, 2007
Hi,
Please help me in this problem...
i am new to sql server..
i am using sql server version 8...(doesnot support function with retun values..)
so i have created a procedure...
-----------procedure------------------(to find next monday after 6 months)-------------------
[code]
create proc next_Monday ( @myDate DATETIME )
as
BEGIN
set @myDate = dateadd(mm, 6, @myDate)
while datepart(dw,@myDate) <> 2
begin
set @myDate = dateadd(dd, 1, @myDate)
end
select @myDate
end
go
[/code]
--------------------------------------------------------
i can able to execute this procedure separately.... working well...
but don't know how to call it inside another query....
the following throws error....
select smaster.sname, smaster.Datex, 'xxx'=(execute next_monday smaster.Datex) from smaster
please help me... how to fix this problem...
View 16 Replies
View Related
Aug 25, 2006
Hello,
I am trying to figure out how to use the select maximum command in SQL Server 2005. I have already created a database and I have it populate it with multiple fields and multiple records. I Would like to create a new column or field which contains the maximum value from four of the fields. I have already created a column and I am trying to figure out how to use a command or SQL statement which is entered into the computed equation or formula in the properties for this field/column.
Any help you can provide will be greatly appreciated!
Thank you,
Nathan
View 17 Replies
View Related
Aug 18, 2014
I have date field in table and data contain 2014-08-09 11:13:03.340
when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03
Why the 03 is trimming in smalldatefield.
how do I use select query to get 2014-08-09 11:13:03 from original Date records
View 1 Replies
View Related
Mar 3, 2015
I have a scenario,
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
Equipment table looks like :
Equipment_ID Code DescriptionTreeLevelParent_Id
6132 S2611aaa 4 6130
11165 V2546bbb 3 1022
15211 PF_EUccc 5 15192
39539 VP266ddd 4 35200
5696 KA273eee 3 3215
39307 VM2611fff 4 35163
39398 IK264ggg 4 35177
There is another table for Equipment_Tree which has got Equipment_Tree_ID,Parent_Id and Equipment_ID does not has the Code here.
Select query where i need to select the Code for all Parent_Id's.
View 8 Replies
View Related
Dec 4, 2007
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks
View 7 Replies
View Related
Jul 9, 2002
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
View 1 Replies
View Related
Jul 23, 2014
I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.
Now i want to call stored procedure in select statement because stored procedure return a single value.
I search on google and i find openrowset but this generate a meta data error
So how i can resolve it ???
View 7 Replies
View Related
Sep 9, 2014
I am writing a stored procedure and have a query where I create a variable from other table
Declare @Sem varchar (12) Null
@Decision varchar(1) Null
Select emplid,name, Semester
Decision1=(select * from tbldecision where reader=1)
Decision2=(select * from tbldecision where reader=2)
Where Semester=@Sem
And Decision1=@Decision
But I am getting error for Decision1 , Decision2. How can I do that.
View 6 Replies
View Related
Mar 18, 2015
How to convert float to timestamp in single select query
E.g., i have float as 1.251152515236 ,
I want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
View 1 Replies
View Related
May 25, 2015
Here the SELECT query is fetching the records corresponding to ITEM_DESCRIPTION in 5 separate transactions. How to change the cursor to display the 5 records in at a time in single transactions.
CREATE TABLE #ITEMS (ITEM_ID uniqueidentifier NOT NULL, ITEM_DESCRIPTION VARCHAR(250) NOT NULL)INSERT INTO #ITEMSVALUES(NEWID(), 'This is a wonderful car'),(NEWID(), 'This is a fast bike'),(NEWID(), 'This is a expensive aeroplane'),(NEWID(), 'This is a cheap bicycle'),(NEWID(), 'This is a dream holiday')
---
DECLARE @ITEM_ID uniqueidentifier
DECLARE ITEM_CURSOR CURSOR
[Code] ....
View 1 Replies
View Related
Aug 20, 2015
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
View 6 Replies
View Related
Feb 17, 2008
I am new to web services and as a DBA I only have limited .net experience. Our development team is creating a web services interface for our new IVR phone system, and we've decided on a solution that will send each of the phone call parameters from our database to the phone system in an XML based on line number and extension. I've found that Http Endpoints in sql server will be a perfect solution for exactly that and I won't need any .net to develop it. Thats the good news. There are, however, other requirements for our phone system to communicate back to our database. All of the examples I've seen for endpoints use a stored procedure performing a select statement.
Is it possible to create an endpoint that references a stored procedure performing an insert or update? To be more specific, can I create an endpoint that references a stored procedure that has parameters? If so, how do I pass those parameters through a web service request? Is it as simple as adding &variable="value" to the url?
If so I could develop this entire solution from the database side and not involve our .net programmer!
Another alternative: the phone system does have it own database in sql server. I'm not sure if our phone system provided can or will do this, but if they are willing to work with this, maybe a service broker can fit into this solution.
I look forward to any response. Thanks!
View 5 Replies
View Related
