I have what i believe to be a pretty basic SQL question. I have 2 tables, One called participant, and another called cnAvailableParticipant_List they are related with a field called userid I would like to select the firstname, lastname, userid, fields from the participant table and the dnnuserid field from the cnAvailableParticipant_List table where the users have a record in the participant table but do NOT have a record in the other table. Basicially i want to select all the users who are essentially not checked in. So far i have:
SELECT p.ParticipantID as ID, p.FName as FName, p.LName as LName, ap.DNNID FROM Participant p, cnAvailableParticipant_List ap
WHERE
ORDER BY LName, FName
can someone explain to me what i need in the where clause and why. I don't just want the answer, i would like to know why it works the way it does as well. Sorry for the basic question but im new to SQL
I have an sql data source..select command is something like this. Select password from users where username = Username.Text how can i retrieve the password value and save it into a string?! to make a comparison between password value and a textbox Password.Text i know its a silly question but i took me long time and i still can't find the ans
I'm new to SQL and learning, so please forgive my question....
I have two tables; Authors and Books with the fields as shown below. Each author will have 1 or more books listed in the Books table.
I need a SELECT statement that will GROUP all authors and return 1 record per author with the LAST published book fields for each author (most recent PubDate) from the Books table. All fields on both tables should be returned:
Basically, im being asked Which is the maximal budget of projects in which employee 'John Smith' is involved?
I got a nested query done kinda right, like this
Select max(Budget) From aProject Where (aProject.Pid = ( Select Pid From aWorks_For Where aWorks_For.Eid = ( Select Eid From aEmployee Where Ename = 'John Smith')))
But when an employee is working on more then one project I get this error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
If I want to select certain values from a table where date is equal to something and the second field is null,would I go.
select * from table where date = 'something' and Sent = null or something else because if I do it this way I get nothing as a result but if I scroll down I can see that I have null values.
This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...
I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.
SELECT * FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE '%' + @binomen + '%' AND Locality LIKE '%' + @locality + '%' AND ParentID1 = COALESCE(@taxparent, ParentID3) OR ParentID2 = COALESCE(@taxparent, ParentID2) OR ParentID3 = COALESCE(@taxparent, ParentID3) OR ParentID4 = COALESCE(@taxparent, ParentID4) OR ParentID5 = COALESCE(@taxparent, ParentID5) OR ParentID6 = COALESCE(@taxparent, ParentID6) OR ParentID7 = COALESCE(@taxparent, ParentID7) OR ParentID8 = COALESCE(@taxparent, ParentID8)
The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.
Has anyone got a good tip to help me resolve this puzzle?
Hi! I am trying to do something that I would think should be rather simple.I have a SQLDataSource that has a simple select statement.I have a String Variable st_tempI know my SQLDataSource returns only one value, and it's a string.HOW DO I DO somehitng like st_temp=SQLDataSource.Select;I am getting nothing but errorrs Thanks in advance Dan
Hi,I feel stupid for posting this question but I cannot find out how to create an SQL statement to return records that have been added today.My database table has a field called calldate which hold the date and time of the record added to the database i.e. "02/08/2005 16:55:41".Please can someone let me know how I can search to find all records added today. I thought it would be something like it, but I assume I need to format the calldate so it's just 02/08/2005.Select *From TableWhere CallDate = GetDate()ThanksBrett
I am learing VWD as I build a site and while I can use the various controls (GridView, FormView) to set up operations and retrieve/display database information, I need to be able to codebehind to do SQL operations for retrieving data to fill paper forms and to modify the database. After I retrieve data from the database, I need to do a lot of computation befor I build a paper quote that is emailed to a customer. I have written a subroutine to which I pass my sql query statement and I can add a new entry and edit an existing entry. The problem is a simple query to read an existing database entry. If I write a simple command : Cmd = SELECT PartNumber, PartName, PartsAvailable FROM Inventory WHERE PartNumber = "P7226" , the command is executed but the return data falls on the floor somewhere as I dont know how to code to put the results in an array or in fact, have any idea on how to find the returned data. Can anyone help with this simple(?) problem? My sub that handles the sql command is shown below. I have also been looking for the way to code in VB to cause existing controls like GridViews to access, edit, and insert but I havent been able to find a paper or tutorial. If anyone has seen this information, it would be helpful also.Protected Sub CallSQL(ByVal Cmd As String) Dim conn As New SqlConnection("Data Source=.SQLExpress;AttachDbFileName=C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True") 'Data Source=.SQLEXPRESS;AttachDbFilename="C:Documents and SettingsaMy DocumentsVisual Studio 2005WebSitesWebSite5App_DataPINEMgt.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True)Dim sql As String = String.Empty sql = Cmd 'Open the connection to the database and execute he SQL conn.Open()Dim command As New SqlCommand(sql, conn) command.ExecuteNonQuery() conn.Close() End Sub
This is probably a very silly question.I started learning ASP.net by following ASP.NET Unleashed. I am stuck where he wants me to open a connection to SQL Server database. I have just downloaded MSDE. But I dont know where to type this code and how to run it..so as to connect to the database.
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load Dim conPubs As SqlConnection
conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" ) conPubs.Open() End Sub
</Script>
Connection Opened!
Now do i have to change the uid to SA ? (i had to assign one when i downloaded and installed MSDE?
am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:
1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then 2. Run the dts task (which i have created and is working)
In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!
I downloaded SQLExpress and Visual Studio Express to my home computer. I built a simple database, adding data through theSQLexpress admin tool. I built a web page using MS Studio. I connected to the database and used the webpage for a few days. Then I restarted the computer. Now the web page won't open, and MS Studio won't open the MDF file in the App_Data folder. I can still see and work in the database through SQL server Express. The web page and the MSStudio attempt to connect to the mdf file both fail with this message: Cannot open user default database. Login failed.Login failed for user 'KAAAK/Administrator'. So it seems to be trying to connect as the Windows user. When I try to modify the connection to connect through a user/password I created in SQL manager, I get a message that the user is not a trusted SQL user. from web.config: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|info.mdf;Integrated Security=True;User Instance=True;User ID=Admin;Password=12345" providerName="System.Data.SqlClient"/> </connectionStrings> That was changed from the original string created automatically by MS Studio <connectionStrings> <add name="stocksConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|stocks.mdf;Integrated Security=True;User Instance=True;" providerName="System.Data.SqlClient"/> </connectionStrings> I am sure this is some simple problem, but why would the system refuse to access an mdf file it had already been accessing.? Thanks, Michael
Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row. When it gets to the third line the page crashes with an error. SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True"); SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection); SqlDataReader reader = cmd.ExecuteReader();
is there somewhere i need to put in my username or password? or is this code just wrong
not sure why I am having trouble here but I am using the following WHERE clause expecting to find all rows where any one of the the three keywords are present. ....WHERE Company.L_Keywords LIKE '%metal%' AND Company.L_Keywords LIKE '%tile%' AND Company.L_Keywords LIKE '%ceramic%' however it appears to finding only the rows where all three words are present in the L_Keywords field
This is a very simple question. How would a select satement be formated in the following example. SELECT Grade, Student_ID, First_Name, Last_Name FROM Scores WHERE (This is where I'm stuck and I know this is not the right formatting although I wish it were because it would make my life a little bit easier.) Student_ID = 115485, 115856, 568547, 965864, etc... I may have up to 100 specific student ids to put in this one statement. I know I can use the "WHERE Student_ID = 115485 OR Student_ID = 115856, OR Student_ID = 568547" but that would be alot of waste. Seems like there should be an easier way than using the "OR Student_ID =" for every entry. Can someone explain another way I can do this. Thanks in advance.
Hey, I have a pretty simple question.My query is throwing an error saying "Invalid column name 'subject'."The problem is that subject is a custom column I've made, well just look at the sql:SELECT a.ArticleID, subject=ISNULL((select subject from subjects),'') where subject='some subject'
I have already created package which loads a text file to database using the dts wizard in Enterprise Manager.How do I execute that package using visual basic?Please provide the Code!!!Thanks
Hi there. I am trying to use MDX code to create a measure in ProClarity. Please help!!
A store can be one of a number of ‘Brands’. The MDX segment below gives me the Sales Value of a selected item, but for Store Brand 'Brand1' only. This works fine - but how do I add a brand? That is, how do I see the combined Sales Value for the selected item for 'Brand1' and 'Brand2'?
Hoping someone could me with an ongoing indexing question that I have.
On my site, we have over the past 5 years developed what is emerging as a fairly complicated dbase structure, as features have been added to my site and relations have increased between different database tables, there has been a need to index fields in different ways, and in some instances field indexing has overlapped. For example we may have a table that has 5 fields (field1,field2,field3,field4,field5). A need to index field1 is requried because of a query that reads:
SELECT * From Table1 where field1=XXXXX
Additionally there may be a need to for another query that reads:
SELECT * From Table1 where field2=XXXXX
In this instance an index is placed on field2.... But, for example when there is the following query:
SELECT * From Table1 where field1=XXXXX and field2 = XXXXX
Is it necessary to set a new index on: field1,field2 ???
We have made the choice that yes, in fact there is...but now over time some of our tables have instances of single fields being indexed along with combinations of two single fields that have already been indexed, being indexed together. As tables have grown to over 1,000,000 records and having up to 15 or so indexes, we realize that the number of indexes maybe degrading performance. Also, indexes vary in type, e.g INT,BIGINT,Varchar fields... In the above instance, can we eliminate the multi-indexes and improve performance over all...?
On a second related question:
In the event that two tables are joined on a common field.
e.g. Select * from Table1,Table2 where Table1.field1=Table2.field1
Is it necessary to index both of these fields in tables: Table1 and Table2 ?
Hope someone can help, as we are looking to improve the efficiency of our tables as they continue to grow.
I need help. 160821A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information. I need help. Our SQL Server is crashing. The Database is still recovering. I can not kill any Process when the Server crashed. I shoot down and restarted the server. Nothing to do. I can not access to the database. It is recovering. How long? I do not know. What can be the reason of the recovering? Nor the event log of Windows NT or the log files of the SQL SERVER can help me.
Here is a part of the log file
23216Arithmetic overflow error for type %s, value = %f.
10915There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
99/01/19 01:14:25.69 spid25 bufwait: timeout, BUF_IO, bp 0x1bba600, pg 0x11b50, stat 0x801000/0x6, obj 0x23494814, bpss 0x124a2a0 99/01/19 01:14:27.15 ods Error : 17824, Severity: 10, State: 0 99/01/19 01:14:27.15 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'myserver'. 99/01/19 01:14:27.15 ods OS Error : 232, The pipe is being closed.
I have many views and views on these views. Is there anyone out there who might have a suggestion as to how I can keep them straight so that I won't lose track of what I made them for? I am starting to not remember why I made some, so I have to go back and try and remember. Any suggestion would be appreciated.
I have a new pc with xp on it,I have been coping data,files,projects,etc from my old pc onto my new over the network.
If I open Query Analyzer my databasese are there and it is all fine,but if I open ODBC I have no databases under System DSN. How do I go about that,I have many databases and tables in it,is there a way I can refresh it or something,it would have to be something simple,right???
Hi, can somebody help me with some verification SQL commands that I need to run on an Oracle database?
In the Oracle table 'FEE', if the 'FEETYPE' column has a value of '3' for a record, then the 'PERCENTAMOUNT' column needs to be not null for that row - can somebody put this into a command for me so I can make lots like it?
another one I have is in the 'FEE' table, if the 'FEETYPE' column has a value of '1', the 'PERIOD' column needs to always have a value of '3' for that row - is there a command that can check the records that do/do not have this information?
I was just looking for opinions on the best place to pick up SQL skills. Community college course? Online courses? Grad school? I've finished undergrad, if that helps anyone have an idea of my current situation.
I have only been working with SQL for short time. My question is probably a basic one but I would appreciate the assistance!
The scenario is I have two different tables AVCfids (on ACC database)& AVCfids (on ACC.archive database). I am trying to write a command in SQL server agent (jobs) to transfer the data on a daily basis from AVCfids to ACC.archives. The script I have created but is not working is:
Hi, I am totally new to sql server and have got a few doubts.I couldnt find it in earlier posts.Sorry if it is a repetition
*Can we get all the table names of a database by Query? *How to create a similar copy of a table by using AS..does this work in sql server 2000?(tried but showing error) *can we modify the column properties(like ensuring NOT NULL) after the table is created?
