Im using windows xp sp2 and MS Sql server 2005 enterprise edition may b and im also havin sql server express
previously when i installed sql server express it took server name on its own and i used to run queries using AdventureWorks database..
Recently i formated my desktop and again installed both the versions im having but neither is taking server name.. and i dint noticed what was the server name before.. im a student home user i just hav to query on adventurework databases on the same computer i cant afford to buy any servers..
so can anyone tell me how can i configure sql so that i can query !
Im considering several options as to where to place the SQL Server and the web server in our organization's network I would like to hear your insights about the subject and the following options:
Option 1: Placing the SQL Server inside the domain while the web servers are outside in the DMZ
Pros: SQL Server is in the domain, its harder to hack and its easier to manage inside the domain
Cons:the DMZ must allow access inside the domain so that the web servers can access the SQL Server this is not very secure.
Option 2: Placing both SQL Server and the web servers in the DMZ
Pros: Fast access from web servers to the SQL Server. no traffic is allowed from the DMZ into the domain so the domain is completely secured.
Cons: The SQL Server is not very secured, its harder to administer it outside the domain.
Hi, I have Sql server 2005 Standard edition on my system and was wondering whats the difference between Standard edition and developer edition which one is better..most of the things that i do on sql server is write sprocs, create table etc... any ideas will be appreciated.. regards Karen
I see the following error on the sql server log of one of our sql servers running sql 2000 with sp4. SQL Server has encountered 1964 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [h: empdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000534. The offset of the latest long IO is: 0x0000002b09e000
Any idea as to what might be causing this error. Appreciate any comments.
<usualDisclaimer>Please forgive me if this is in the wrong group, and if so,what is the right group.</usualDisclaimer>Let me start off by first saying im a newb. Ok, with that out of the way Iam trying really hard and boy have I learned a lot in the last little whilebut I have a question i just can't seem to find a good answer to.Lets say i have a table that simply stores how many times someone has loggedinto a webpage. Is it better to store each login as a new record then countthe records or is it better to simply have one record that updates the totalvalue by incrementing that one field. I have read all manner of articlesand some say one way is better vs. the other but what I can't find is why?If I knew why one way was better than another then I could make and educateddecision and choose the best way that is right for me. Is updating more orless expensive then inserting? Does it matter and is a relevant question?And before anyone comments on my use of all uppercase letters for my tablename I do this so that my table names stand out within the sql serverenterprise manager. In other words system tables are lowercase and mytables are uppercase. People always seem to give me crap for this but neverback it up with a good explanation so far as I can tell its personalpreference? Am I wrong?CREATE TABLE USER_METRICS(-- here i can select all the records and count them up.ID INT IDENTITY(1,1) PRIMARY KEY,Email VARCHAR(250));-- Or this?CREATE TABLE USER_METRICS(-- and of course here can i simply retrieve the value of Total_LoginsID INT IDENTITY(1,1) PRIMARY KEY,Email VARCHAR(250) NOT NULL,Total_Logins INT);Regards,Muhd
Hello DBA's i use this proc to insert & update the records in the databse update works good untill i change the user id. userid can be changed where adminuserid is the IDENTITY coulmn in the table.ALTER PROCEDURE [dbo].[spinsertusers] -- Add the parameters for the stored procedure here @adminuserid varchar(36), @userid varchar(15), @fname varchar(25), @mname varchar(25), @lname varchar(25), @password varchar(15), @address1 varchar(255), @address2 varchar(255), @postcode varchar(15), @cityidentity varchar(36), @dob smalldatetime , @email varchar(50), @crtduser varchar(36), @crtdon datetime, @isactive char(4), @mode char(10), @reccount INT output AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @Count INT -- Insert statements for procedure here IF (@mode='insert') SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid) IF @count=0 --Inserting the Records BEGIN INSERT INTO [school].[dbo].[AdminUserMaster] ([AdminUserIdentity] ,[FirstName] ,[MiddleName] ,[LastName] ,[UserID] ,[Password] ,[Address1] ,[Address2] ,[PostCode] ,[CityIdentity] ,[DOB] , ,[CreatedBy] ,[CreatedOn] ,[IsActive]) VALUES ( @adminuserid , @fname , @mname , @lname , @userid , @password, @address1, @address2, @postcode, @cityidentity , @dob, @email, @crtduser, @crtdon , @isactive ) SET @reccount=2 return @reccount END
-- End of Query IF (@mode='update')
SET @Count=(SELECT COUNT(*) FROM adminusermaster WHERE userid=@userid) IF @Count=1 --Query for Update the Records BEGIN update AdminUserMaster set [FirstName]=@fname , [MiddleName] =@mname, [LastName]=@lname, [userid]=@userid , [Address1]=@address1, [Address2]=@address2, [PostCode]=@postcode, [CityIdentity]=@cityidentity, [DOB]=@dob, =@email, ModifiedBy=@crtduser , ModifiedOn=@crtdon , isactive='Y' where AdminUserIdentity= @adminuserid set @reccount=3 return @reccount END
]First of all here is how i created my SQL database and established a connection in visual studio 05. I go into the server explorer and right click on the data connections node and and select "create new SQL database". A window then prompts me for a server name and data base name. Under server name I write "localhostSQLEXPRESS" and under data base name I put in whatever. This totally worked and the database was asigned the extension of .DBO I can now insert, select and delete information with some simple ADO coding. The only problem is that using this method makes it seamingly impossible to FTP my database onto my host server. I am aware that I can create a data base in the APP folder by simply right clicking it and selecting " new database". This would simplify the deploying process but I cant seem to establish a connection this way. Ive also noticed that when I use this method that it saves it as a .MDF instead of a .DBO. What is the difference? Why does my .DBO work but not my .MDF in my app folder? Im so confused over this issue that I am problably not even asking the right question. Bottom line is that I have a small web application with a .dbo database that works wonderfully in my isolated visual studio environment and I need a solution to deploy it on the world wide web. Please help with any information I would be very thankfull.
I am not an expert with sp. I am trying to pass the column name as a parameter but I get the following error "Must declare the variable '@strColumnName'."
ALTER PROCEDURE dbo.TempGetDataForDropDown ( @strColumnNamenvarchar (50) ) as
Hi all, I've recently created a shopping cart system that uses SQL Server on the backend. The shopping cart will add a record to a table called CartEvents each time anything happens, like view item, update, delete etc. anyhow, the site has been up for about a week now and that particular table has about 4200 records already, I project tens of thousands of records in that table, so how many records is too many records? Whats your experience with tables that have hundreds of thousands of records? How many records can SQL server handle and still perform well?
I've been writing a few sql queries over the past few days, and would like to know what the difference is between:
website is null
website like ''
Becasue if they mean different things i want to know how to look them both up at the same time. Obv im after data that is blank or has nothing written in it.
I am trying to run an sql query with a function in SQL SERVER MANAGEMENT STUDIO and am getting the following error.
Msg 207, Level 16, State 1, Line 9 Invalid column name 'UserID'.
here is my Query ----------------- USE vportal4vsearch GO CREATE FUNCTION dbo.ConcatUserGroups(@UserID int) RETURNS VARCHAR(8000) AS BEGIN DECLARE @groupname Varchar(2000) SELECT @groupname = COALESCE(u.group_name + ', ', '') + CONVERT(varchar(20), u.group_name) FROMdbo.[user] A JOIN dbo.UserGroup_combo UG ON UG.ug_userID = a.USER_ID JOIN dbo.usergroup U ON UG.ug_groupID = u.group_ID WHEREA.USER_ID = @UserID ORDER BY U.GROUP_NAME
RETURN @groupname END GO SELECT user_ID, user_Firstname, user_Lastname, user_Email, Username = (Case When user_Username = ' ' or user_username is Null then 'None' else User_Username end), user_creationdate, Active = (Case When user_Active = 1 then 'Yes' else 'No' end), dbo.ConcatUserGroups(UserID), TheatreCode = (Case When User_TheaterCode = ' ' or User_TheaterCode is Null then 'N/A' else User_TheaterCode end), JobTitle = (Case When user_JobTitle = ' ' or User_Jobtitle is Null then 'N/A' else User_Jobtitle end) FROM [user] GO DROP FUNCTION dbo.ConcatUserGroups GO
Private Sub pulllikehell() Dim cn As SqlCeConnection Dim rda As SqlCeRemoteDataAccess = Nothing Dim sqlEngine As SqlCeEngine
Try
' Create database if it doesn't already exist If (Not File.Exists("My DocumentsORDER.sdf")) Then sqlEngine = New SqlCeEngine sqlEngine.LocalConnectionString = "Data Source=My DocumentsORDER.sdf;"
sqlEngine.CreateDatabase() sqlEngine.Dispose() Else ' Open the connection to the database cn = New SqlCeConnection("Data Source=My Documents" & _ "ORDER.sdf:") cn.Open() Dim cmd As SqlCeCommand = cn.CreateCommand()
' Drop the FieldMemos table cmd.CommandText = "DROP TABLE STAFF" cmd.ExecuteNonQuery()
' Close the connection
End If
' Instantiate the RDA Object rda = New SqlCeRemoteDataAccess ' Connection String to the SQL Server. Dim remoteConnectString As String = "Provider=SQLOLEDB;" & _ "Data Source=KANGALERT/SQLEXPRESS;" & _ "Initial Catalog=ORDER;"
rda.Pull("STAFF", "Select * from STAFF", remoteConnectString, _ RdaTrackOption.TrackingOnWithIndexes) MsgBox("ok")
Catch sqlex As SqlCeException Dim sqlError As SqlCeError For Each sqlError In sqlex.Errors MessageBox.Show(sqlError.Message) Next Catch ex As Exception MessageBox.Show(ex.Message) Finally rda.Dispose() End Try
End Sub
[/CODE]
computer server name is kangalert
and got 2 sqlservice run in my pc, SQLEXPRESS and MSSQLSERVER
I have a query that must return information for an invoice. It is not straight forward though. Some things need to be displayed from the if something else is happening... For example...declare @id int declare @chargeid int set @id = 1 set @chargeid = 9
declare @pp bit, @pages int, @pagefee money, @pagecharge money set @pp = (select perpage from tblmainfees where mainfeesid = @chargeid) if (@pp = 1) begin set @pages = (select pages from tblcharges inner join tblrequest on requestid = fkrequestid where requestid = @id) if (@pages > 0) begin set @pagefee = (select perpagefee from tblmainfees where mainfeesid = @chargeid) set @pagecharge = (@pages * @pagefee) end end ...
select @pagecharge as PerPageCharge
Or should I do some complex query with only one select?
Im a bit confused as to what is best. I have a web site built in c# web dev which utilises a data base. What is best practise ........... 1. To have one sql data source on one page and all other pages use that source. 2.. each page has its own source and connection string depending on its data requirements. 3..... have no source and use insert statements with stored procedures because most of my site extracts data from visitors rather than displaying data to them. If I have this right.......the data source is a copy of the data base that is available for use to the visitor or is it just part of the connection to the data base. My only gripe with the whole development process has been the discovery that I have learnt four ways of doing the same thing.......which in its self becomes confusing.. However one thing has stood above all else ........this forum richard
1 SELECT 2 RowNumber, 3 'Source.Dbf, Plan.Dbf', 4 'Source Name is missing for Source Number "' + IsNull(RTrim(f.SOURCE_NUM),'Unknown') + '" in Plan.Dbf table.' 5 FROM 6 SourceDbf f 7 JOIN 8 ( 9 SELECT DISTINCT 10 SOURCE_NUM, 11 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME 12 WHEN 2 Then SRC2NAME 13 WHEN 3 THEN SRC3NAME 14 WHEN 4 THEN SRC4NAME 15 WHEN 5 THEN SRC5NAME 16 WHEN 6 THEN SRC6NAME 17 WHEN 7 THEN SRC7NAME 18 WHEN 8 THEN SRC8NAME 19 WHEN 9 THEN SRC9NAME 20 WHEN 10 THEN SRC10NAME 21 WHEN 11 THEN SRC11NAME 22 WHEN 12 THEN SRC12NAME 23 WHEN 13 THEN SRC13NAME 24 WHEN 14 THEN SRC14NAME 25 WHEN 15 THEN SRC15NAME 26 END 27 FROM 28 PlanDBF p 29 Where 30 p.PLAN_NUM = s.PLAN_NUM 31 ) as SourceName 32 FROM 33 SourceDBF s ) c on f.PLAN_NUM = c.PLAN_NUM
i am getting an error on Line 33 and this what the error says... Msg 207, Level 16, State 1, Line 33Invalid column name 'PLAN_NUM'.
I have tried running this query multiple times with no success I get the following errorIncorrect syntax near '('.I tried with quotes and without quotes around the 10 and also without the brackets around variable. It runs when an integer in entered in the variables place but that is not what I want. What am I doing wrong DECLARE @p AS intSET @p='10'SELECT TOP (@p)* FROM my_tbl order by newid()
Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30456: 'InserParameters' is not a member of 'System.Web.UI.WebControls.SqlDataSource'.Source Error:
Line 15: registrationDataSource.InsertCommand = "INSERT TO Reputation (firstname, lastname)VALUES(@First Name, @Last Name)" Line 16: Line 17: registrationDataSource.InserParameters.Add("firstname", firstname.txt) Line 18: registrationDataSource.InserParameters.Add("lastname", lastname.txt) Line 19: Source File: C:UsersQaiphyx eputationDefault.aspx.vb Line: 17 Show Detailed Compiler Output:
strCommand = "SELECT * FROM tblevents WHERE startingDate=#"&startDate &"# AND eventtitle like '%"&criteria &"%' ORDER BY " &sSortSt I want to find any records that match a certain keyword on a specific date... But nothing comes up even though there is an event matching that criteria on the given date. Do I need brackets around my query or something?
I have SQL, I was an MS Access Developer. In Access connection string are easy. But SQL they are more detailed. I just want to know how do I find: Server Name - server=localhost User Id - uid=sa Password - pwd=secret; Database Name - I know the database name Please some insight.
EXEC sp_addlinkedserver @server = 'LinkedServer', --Server Name @provider = 'Microsoft.Jet.OLEDB.4.0', --Provider Name @srvproduct = 'OLE DB Provider for Jet', --Product @datasrc = 'C:DataX2000Master.mdb' --Data Source
Before the above query was okay..... I used to run the same query as I today ran but I got this error message, any help pls.....
Server: Msg 7370, Level 16, State 2, Line 1 One or more properties could not be set on the query for OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. The provider could not support a required property.
I'm working on an mini intranet site for a "University" type style. I have a table called "events" which stores information. One of my columns is "eventRSVP" which stores the employees ID's who click on the button, separated by a comma.
example: Quote: 1751,2179,1371,2314,1778,702,1607,798,2097 Would it be better to create a new table called something like "event_jn" and it has 3 columns.
I'm also redoing our whole intranet and would be doing a lot of these similar type id linking. So my other question is, the more tables I have, is it going to slow down the response time?
From Traveler_X3 trv Join Recipe_X3 rcp On (trv.RecipeID = rcp.RecipeID)
Where trv.Oper = rtsA.oper And trv.RouteGroupID = ( Select rgp.routeGroupID
From Route_Group_X3 rgp
Where rgp.routeGroupName = ( Select rtl.RouteGroup
From RouteList_X3 rtl
WHERE rtl.route = rtsA.route ) ) ))
FROM Routes_X3 rtsA
where rtsA.route=( SELECT lhm.route
FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o
WHERE lhm.lot = 'S6D0IQ002A' AND lhm.oper = x3o.oper AND lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o WHERE lhm.lot = 'S6D0IQ002A' AND lhm.oper = x3o.oper) )
and rtsA.seq BETWEEN ( SELECT seq
FROM Routes_X3
WHERE oper =( SELECT lhm.oper
FROM brettb.pdash2.dbo.lothistorymoves lhm
WHERE lhm.lot ='S6D0IQ002A' AND lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm WHERE lhm.lot = 'S6D0IQ002A') )
and route=( select top 1 lhm.route
FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o
WHERE lhm.lot = 'S6D0IQ002A' AND lhm.oper = x3o.oper AND lhm.date_time = (SELECT max(date_time) FROM brettb.pdash2.dbo.lothistorymoves lhm, x3oprs x3o WHERE lhm.lot ='S6D0IQ002A' AND lhm.oper = x3o.oper) ) )+1 and 219;
What i got working was to put the query in a temp table called #temp1 and then run this: select sum(theoreticalTime) from #temp1;
Hi, I recently modified all the messages with severity 15 and 16 to be logged in windows applications log... I then discovered that my Application is generating lots of errors of type:
Error: 16945, Severity: 16, State: 1 The cursor was not declared.
followed by
Error: 16955, Severity: 16, State: 2 Could not create an acceptable cursor.
I think this error is occuring since a lot, but I discovered it now because I recently modified which messages are logged (I added those with severity of 15 and 16).
This error is occuring everytime (one error every 3 minutes!!)... I dont know what part of my app is having trouble, i think its a bug of sql server... Does somebody know anything about this error?
