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.
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)
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.
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)
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
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?
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?
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.
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)
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
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.
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
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....
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?
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
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.
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
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:
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
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...
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!
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.
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
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
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.
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
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.
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.
