Original suggestion for my problem was - Select * from TableA where ID not in ( Select ID from TableB)
When I run the query below using the IN operator I get 227 records returned but when I use NOT IN I get zero records when I expect well over 10,000. What am I missing?
using SQL 2000 server
SELECT LinksInfo.L_ID, LinksInfo.C_ID, Companies.C_CompanyName, Companies.C_Email, Companies.C_CompanyEmail
FROM LinksInfo INNER JOIN Companies ON LinksInfo.C_ID = Companies.C_ID
WHERE (LinksInfo.L_ID IN
(SELECT ZL_ID FROM Location_Zip))
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
Hello again, I think im missing something here, i just cant find out what it is. I have a temp table: CREATE TABLE #tempSearch(tempID BIGINT IDENTITY(1,1) PRIMARY KEY,username NVARCHAR(20) COLLATE Finnish_Swedish_CI_AS,lastlogin DATETIME,signupdate DATETIME) Now i am trying to retrieve some data for each user that is inside this tempSearch list and have an id over xxx (xxx = the value of the parameter @first_id): SELECT @sql = 'SELECT profile_publicinfo.username, profile_publicinfo.gender, profile_publicinfo.signupdate, profile_profilephoto.imageurl, profile_profilephoto.alttext, settings_username.color, profile_publicinfo.lastloginFROM #tempSearch INNER JOIN dbo.profile_publicinfo ON profile_publicinfo.username = #tempSearch.usernameINNER JOIN dbo.settings_privateinfo ON settings_privateinfo.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.profile_coolfacts ON profile_coolfacts.username = profile_publicinfo.username FULL OUTER JOIN dbo.profile_profilephoto ON profile_profilephoto.username = profile_publicinfo.usernameFULL OUTER JOIN dbo.settings_username ON settings_username.username = profile_publicinfo.usernameWHERE (profile_publicinfo.username IN (SELECT username FROM #tempSearch))AND #tempSearch.tempID >= @first_id' SELECT @paramlist = '@first_id int'EXEC sp_executesql @sql, @paramlist, @first_id I need to get the tempID from the tempSearch table in order to compare it with @first_id When i run this i get the same username repeated like 30 times then it moves over to the next, when i debug the #tempSearch it looks fine, just the users that are suppose to be there.
Hi I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system? All help appreciated
OK so I'm using dynamic sql to enable me to have a variable for the order by, but now it's giving me the following error
Syntax error converting character string to smalldatetime data type.
Any help is very much appreciated
Code follows
CREATE PROCEDURE spBattingAve1 @cid datetime, @cid1 datetime, @sid nvarchar(10) AS
DECLARE @query nvarchar(4000)
SET @query = 'SELECT MAX(t_batting.runs) AS BestRuns, COUNT(t_batting.dnb) AS DidNotBat, t_player.surname, t_player.firstname, t_batting.player_id, Sum(t_batting.runs) as SumOfruns, COUNT (*) AS Games, COUNT (t_batting.notout) as nout, (Sum(runs)/(COUNT(*)-(COUNT(notout)+COUNT(dnb)))) as AverageRun, (COUNT(*)-COUNT(dnb)) AS Innings, (SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 50 AND runs <100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overfifty, (SELECT COUNT(*) FROM t_batting inner join t_game on t_batting.game_id = t_game.game_id WHERE (t_batting.player_id = t_player.player_id) AND runs >= 100 and t_game.date >= '+@cid+' And t_game.date <= '+@cid1+' GROUP BY t_batting.player_id) AS Overton FROM t_batting INNER JOIN t_game ON t_batting.game_id = t_game.game_id INNER JOIN t_player ON t_batting.player_id = t_player.player_id WHERE t_batting.player_id = t_player.player_id AND t_game.date >= '+@cid+' And t_game.date <= '+@cid1+'
GROUP BY t_batting.player_id, t_player.surname, t_player.firstname, t_player.player_id HAVING (COUNT(*)-(COUNT(notout)+COUNT(dnb))) <> 0 order by '+@sid+' DESC'
In SQL 7.0, when i use the below query in query analyzer, I got an error message. why? ======================
Set Identity_insert on
UPDATE Table1 SET no = 3 WHERE no = 4
Set Identity_insert off go
Error message ============= Server: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'on'. Server: Msg 156, Level 15, State 1, Line 7 Incorrect syntax near the keyword 'off'.
I am on a box with 4 GB of memory that only runs SQL Server. SQL Server is using 1.7 GB as per task manager. There are a lot of databases on this box. I looked at the boot.ini and /3GB was not set. I have done this at least 50 times before: turn on the /3GB switch and SQL Server will use up to 2.7 GB per task manager. (I know it is actually 2GB and 3GB but it seems that in task manager it shows up as 1.7-1.8 and 2.7-2.8)
Anyway back to the story. So I set /3GB, rebooted the server and SQL Server still only consumes up to 1.7GB. I realize that SQL Server might not actually need more memory, but I have a distinct feeling that it wants more but is being constrained; that is, the the /3GB switch is not working. More relevant information:
1) the boot.ini line: multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3GB
2) max server memory (MB) = 3000
3) min server memory (MB) = 3000
4) awe enabled = 0
5) @@version = 8.00.2039 which is SQL Server 2000 SP4
Has anyone seen this before? Any ideas on how to troubleshoot this?
I would like to get the average of a field but I am not sure if I am doing it correctly.
SELECT dbo.VIEW2.Den AS Den2, CASE WHEN VIEW1.Den = VIEW2.Den THEN 1 ELSE 0 END AS Den3, dbo.VIEW2.ID, AVG(dbo.VIEW2.Den) AS Den4 FROM dbo.VIEW1 CROSS JOIN dbo.VIEW2 WHERE (dbo.VIEW2.ID = '1')
Here is the error I am getting: Column dbo.View2.Den is invalid in the SELECT list because it is not an aggregate function and there is no Group BY CLause.
How can I verify that AWE is actually working? The server is SQL 2000EE on W2KAS with the /pae switch.I've allocated 5000Mb (5Gb) for SQL but Performance monitor says theworking set for the sqlservr process is only 91Mb (it started out ataround 87Mb a couple of days ago and seems to be creeping up)."committed bytes" for the server is about 5.6 Gb out of ~6Gb so there'sa whole pile of memory going somewhere.The error log says "Address Windowing Extensions enabled" (sp_configindicates a "1" for this as well). The "max server memory" item says5000 Mb for its "running" value so within the SQL Server things appearto be correct.The server does seem to be performing better but it's hard to decide ifthat's because of AWE or just the fact that it was rebooted (it hadn'tbeen in a while).- Martin
I've built an application that relies pretty heavily on XML to enableflexible creation of data entry forms and storage of the data enteredinto those forms. Currently, it stores data as an XML document in anntext field. This is great because it allows me a great deal offlexibility in creating fields. However, I need to implement searchingand sorting of that data and I don't know how to do this in an efficientmanner.Currently the data is stored in a format similar to this:<form><fieldname>fieldvalue</fieldname><listfieldname><items><item><label>displaytext</label><value>itemvalue</label></item></items></listfieldname></form>Is there any way to perform searches on data like this, i.e. to searchthe "fieldvalue" in <fieldname> or "itemvalue" in <value>?If there isn't a way to do this, I'm willing to re-structure thedatabast architecture, though the solution would still need to allow thesame kind of flexibility that the XML solution does.Any ideas?Thanks,Kelly
This is a question related to AWE settings. I am on windows 2003 ent version with SQL Server 2005 std version. I have configured SQL Server to use AWE and min memery as 22G and max as 26G (I have 32G in total as physical memory). I do see the message in log as "Windows Address ... Enabled", but when I am using performance monitor to view total memory in kb for SQL Server, it never starts at 22G (usually at 1G). And I doubt why? The account I am running SQL Server is an admin account, so it can definitely locked down the pages in memory. Also, I do think AWE is working somehow, after several hours in running, the total memory will up to 8, 9 or 11G. So, apparently, it is over 2G per process limitation. I still want to know.
1. Is my AWE setting working?
2. If not, how to fix that?
3. How to confirm how many memory is actaully using by SQL Server?
I have a source table in which one of column is numeric data type. I have to do a lookup for this column to a table column which is float data type. So I change this float data type to numeric by convert function. You when I execute the package, if I use data viewer I always see that look up column value is always null. Can any one give me some suggestion please?
Hello All, I am probably doing something small with my query that is causing me pain, but somehow the query is acting funky. What I am trying to do is do a search statement to find documents from a table. But the catch is it is taking three parameters. The searchString, Type and the Location (where the user who is searching belongs to). When I run my query I get all documents where the location and type is correct. But the searchstring does not even work.For example: Lets say I have 3 documents for a LocationID of '2' and the Type for all documents is '0'. Now imagine that the name of the documents as follow: Doc1 = a , Doc2 = b, Doc3 = c. So now a user wants to search for all docs that starts with 'a'. Remember, Loc ID = '2' and Type = '0'. The result of the query should be Doc1 and only Doc1. But somehow I am getting all three Docs b/c they belong and are the type of the give parameters. Any help would be greatfull. Query: SELECT Client.FirstName, Client.LastName, Client.MiddleName, Client.LocID, ClientDocuments.DocID, ClientDocuments.DirName, ClientDocuments.LeafName, ClientDocuments.Type, ClientDocuments.CreatedByUser, ClientDocuments.CreatedDate FROM Client INNER JOIN ClientDocuments ON Client.ClientID = ClientDocuments.ClientID WHERE ClientDocuments.Type = '0' AND Client.LocID = '3' AND ([ClientDocuments.LeafName] LIKE '%' + @SR + '%' OR [Client.SSN] LIKE '%' + @SR + '%' OR [Client.LastName] LIKE '%' + @SR + '%' OR [Client.FirstName] LIKE '%' + @SR + '%' OR [Client.MiddleName] LIKE '%' + @SR + '%' )
I've gotten spoiled with all these query builders. Now in SQL server management studio express its gone. I don't understand whats wrong with their query....any help would be appreciated.
SELECT [Products].myID, [ProductDetails].ShortName FROM [Products] INNER JOIN [ProductDetails] ON [Products].DetailID = [ProductDetails].myID
Parse comes back asCommand(s) completed successfully. Execute comes back asMsg 208, Level 16, State 1, Line 1Invalid object name 'Products'.
Hi All, I have this error: "System.Data.SqlClient.SqlException: Cannot open user default database. Login failed." It occurred when my application try to access the App_Data, because I use Profile to store user data. I can connect to SQL Server just fine, but failed to access the App_Data. How to change the SQLExpress (because I assume that App_Data store its data in SQLExpress) username and password within the application? TIA
Are the sql sentences in mssql identical to mysql? Can someone please show me the easiest way, how to connect to database in asp.net, cause i have no idea how to do that? What do you recommend, should i use mysql or mssql?
I'm trying to use this WHERE Clause in a stored procedure, it is not working. I'm hoping someone can look at it and tell me if there is a better way or what might be causing it to fail.(CONVERT(varchar, tblTasks.ReminderDate, 101) BETWEEN CONVERT(varchar, GETDATE() - 7, 101) AND CONVERT(varchar, GETDATE() + 7, 101))
I have used RAISERROR on some of our pages before, and it worked fine. Now I have a page that has a formview with a sqldatasource that does an insert. If the value for a certain field exists already in the table, I am trying to use RAISERROR('message', 15, 1) to have a popup error. The page does a redirect in the iteminserted event. When I try to insert with data that should cause an error, it doesn't insert into the database, but I don't see an error. The page just redirects... any ideas what could be done to fix this?
Hi Should ASP.Net 1.x work togheter with sql 2005 without problems? I have try to open a web project but I got the following error: Cannot open database "crm" requested by the login. The login failed. Login failed for user 'crmuser'. This crmuser is "promoted" to owner of the crm base, but still I got the problem. I upgrade this web project to ASP.Net 2.0 and I don't have the login problems, that's why I'm wondring. Hope someone can answear me on this question. Thanks! Jan
Hey there, I have a table [user] in a SQLExpress database [Database.mdf] in the App_Data folder. There are two columns. [userId] and [password]. I want to authenticate the user who wants to log in through a Login control. the user should have his userId and password in the [user] table. I digged and made my code. but it doesn't work it keeps giving me that the user is not authenticated. i guess it has no errors. please tell me if you find the error. here is the code: 1 protected void Login1_Authenticate(object sender, AuthenticateEventArgs e) 2 { 3 SqlConnection conExpress = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=Database;Integrated Security=True;User Instance=False"); //Create the server connection 4 5 try 6 { 7 string id_user = Login1.UserName.Trim(); //Get the username from the control 8 string pass_user = Login1.Password; //get the Password from the control 9 10 //These are a sql and connection Examples 11 string sql = "SELECT userId, password FROM user WHERE userId = @param_Id AND password = @param_Password"; 12 SqlCommand comSQL = new SqlCommand(sql, conExpress); //Create the sql command using sql string and sql connection 13 14 //Add the sql parameters 15 comSQL.Parameters.AddWithValue("@param_Id", id_user); //New on VS 2.0 16 comSQL.Parameters.AddWithValue("@param_Password", pass_user); 17 18 string cod_user = ""; 19 string name_user = ""; 20 21 //Open database connection 22 conExpress.Open(); 23 SqlDataReader dr = comSQL.ExecuteReader(); 24 while (dr.Read()) 25 { 26 cod_user = dr.GetValue(0).ToString(); //The coduser is unique onto database table 27 name_user = dr.GetValue(1).ToString(); 28 } 29 conExpress.Close();//Close Database Connection 30 if (cod_user != "") //The user exist onto database 31 { 32 //Create the session vars 33 Session["coduser"] = cod_user; 34 Session["nameuser"] = name_user; 35 e.Authenticated = true; //Grant the access, Goes to DestinationPageUrl 36 } 37 } 38 catch(Exception)//On Login Error 39 { 40 e.Authenticated = false;//Confirm that you are out 41 conExpress.Close();//On any error case, close the database connection 42 } 43 }
I'm trying to create this stored Procedure: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[iumm_PagerData] @PagerIndex INT, @NumRows INT, @PicsCount INT OUTPUT
AS BEGIN SELECT @PicsCount=(SELECT COUNT(*) FROM pics) Declare @startRowIndex INT; set @startRowIndex = (@PagerIndex * @NumRows) + 1;
With PicEntries as ( SELECT ROW_NUMBER() OVER (ORDER BY picID DESC) AS Row, picID, userID, picFileName, votes, date FROM pics) -- Insert statements for procedure here SELECT picID, userID, picFileName FROM pics WHERE Row between @startRowIndex and @StartRowIndex+@NumRows-1 END GO --- However, I'm always getting this error: Msg 207, Level 16, State 1, Procedure iumm_PagerData, Line 22 Invalid column name 'Row'. Msg 207, Level 16, State 1, Procedure iumm_PagerData, Line 22 Invalid column name 'Row'.
Anyone knows what could be the reason for that? Thanks.
Nothing is being inserted into the database. Â My code is below:Line 12: add_friend_source.InsertCommand = "INSERT INTO Friends (UserID, UserName, FriendName, AddedOn, IP) VALUES (@UserID, @UserName, @FriendName, @AddedOn, @IP)"Line 13: detailsview_addfriend.DataSource = add_friend_sourceLine 14: add_friend_source.Insert()Line 15: End Sub
Hello, I am wondering if anyone else has run into problems step debugging using vs 2005 t-sql stored procedures and functions. I have two issues. 1. sometimes my breakpoinst show up as hollow and the step debugger just runs thought 2. other times, I get in and the step debugger works for 4 -10 steps and then locks up. Any ideas are appreciated. If there is a better place for me to post this problem, let me know. I have searched the web on this problem extensively and have not found a solution that fixes my issue. I am logged in with sysadmin which is the most common problem I could find online. Thanks!
