Easiest SQL Question Ever... I Just Can't Work It Out
Apr 4, 2008
I am hoping to pass an auto incrementing number the the database. From the aspx page, I have the following code, but the insert statement does not work as it should right now. Everything worked fine until I added "rank_order" in and the SQL parameters. What am I doing wrong?
Thanks so much :)
<asp:SqlDataSource ID="dsConcepts" runat="server"
SelectCommand="SELECT ID, Name FROM Concepts WHERE ID=@ID"
UpdateCommand="UPDATE Concepts SET Name=@Name WHERE ID=@ID"
InsertCommand="INSERT INTO Concepts (Name, courseID, rank_order) VALUES (@Name,@CID, @rank )">
<InsertParameters>
<asp:QueryStringParameter Name="CID" DefaultValue="-1" QueryStringField="CID" />
</InsertParameters>
<InsertParameters>
<asp:QueryStringParameter Name="rank" DefaultValue="Select Max([rank_order]) + 1 From Concepts WHERE courseID=@CID" QueryStringField="rank" />
</InsertParameters>
<SelectParameters>
<asp:QueryStringParameter Name="ID" DefaultValue="-1" QueryStringField="ID" />
</SelectParameters>
</asp:SqlDataSource>
Hi guys how are you doing? I was wondering how should I set the values in these two variables without making the select, I mean just set, I think that if I select two times it takes much longer is that true?
declare @user_id int, @creation datetime
select @user_id = user_id, @creation = creation from solarUsers
Hi I have a situation where i have a drop down list. The user selects a value from it and then a query behind the scenes is generated based on the value from the list. I want the result of the query to be diplayed in the textbox.I've checked a few examples but i don't really have a good understanding of how to generate this query and get the results to the textbox. I think you use the SQLDATASOURCE but still need some help...Can anyone help? Thanks.
I have a database where several thousand records have NULL in a binary field. I want to change all the NULLs to false. I have Visual Studio 5, and the database is a SQL Server 5 database on a remote server. What is the easiest way to do this? Is there a query I can run that will set all ReNew to false where ReNew is Null? This is a live database so I want to get it right. I can't afford to mess it up.Diane
With classic asp I would do something like this: set conn = server.createobject("ADODB.Connection") conn.open "PROVIDER=SQLOLEDB;DATA SOURCE=sqlservername;UID=username;PWD=password;DATABASE=databasename "set rs = conn.execute("select count(*) from myTable")response.write rs(0)How can I do this with asp.net in the fewest number of lines?
What is the easiest way to insert a new row in a existing sql table through web developer.net (visual basic)? E.g. a database called Names and the columns Firstname and Surname and you want to insert "anna","johnsson"?
Hi, I'm new to SQL Server, and would appreciate some advice on the quickest way to import data from a CSV file.
I've created a database using Visual Web Developer Express, and added a couple of tables. The Help file in SQL Server Express (which is installed on the same PC) indicates that I should use BULK INSERT to populate the table. Only snag is, I could find anywhere to enter the commands! Eventually, I found out about the SQLCMD command which I entered in a Windows Command Window. I successfully connected to the default (SQLEXPRESS) server instance this way, but when I typed USE <my database name> I got an error back saying it couldn't find the database. I know that Visual Web Developer Express by default creates user-specific instances of the database, but I've turned that off (I think!) via the connection string. So, please could someone tell me how I can connect to my database via the SQLCMD command, or alternativley please let me know how else I can bulk inmport data from a CSV file. Many thanks in advance.
im pretty new to SQL and i'm just reading up on full-text searches... i need to do a a full-text search on one table i have in the database.. however I'm reading about full-text indexing/searching and a lot of pages are saying that it uses a lot of resources when searching.. i was wondering how bad is it really? we have about ~100 users who would access the database, probably the peak would be 75 at a time.. would people using a full-text searching slow it down a lot? the servr is a dell poweredge 1750 server, dual 2.8ghz xeon, 1 gig ram.. and also, about the incermental population, if i read right, it populates the catalog each time a item in the table is deleted/inserted/modified.. so would that use a lot of resources as well?
i'm just trying to see if it's worth it to enable full-text indexing for searches on the database if it doesn't slow down the server too much... or are there any better/easier ways to perform searches?
Can anyone recommend the easiest way to get a full copy of a database fromone server to another. The servers are not part of the same organization ornetwork.I have received a backup of the database created with enterprise manager butam unable to restore it into a database of the same name on my server.Thanks,Kevin
I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book Online, but is seemed that only save file to server. How can I save backup file to local computer? or there is other way as easy as this statement for backup/restore database?
-- Create a logical backup device for the full MyNwind backup. USE master EXEC sp_addumpdevice 'disk', 'MyNwind_1', DISK ='c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyNwind_1.dat'
-- Back up the full MyNwind database. BACKUP DATABASE MyNwind TO MyNwind_1
I'm found this sample for BACKUP DATABASE statement in SQL Server 2000 Book Online, but is seemed that only save file to server. How can I save backup file to local computer? or there is other way as easy as this statement for backup/restore database?
-- Create a logical backup device for the full MyNwind backup. USE master EXEC sp_addumpdevice 'disk', 'MyNwind_1', DISK ='c:Program FilesMicrosoft SQL ServerMSSQLBACKUPMyNwind_1.dat'
-- Back up the full MyNwind database. BACKUP DATABASE MyNwind TO MyNwind_1
I am trying to sort out the member "Adams" from the committee members, and sum up the total amounts that were donated while he was the committee member.
The Table:
CREATE TABLE contribution_list (contrib_date DATE NOT NULL, donor_name VARCHAR(30) NOT NULL, amount NUMBER(8,2), program VARCHAR2(30), committee_member VARCHAR2(20) NOT NULL, PRIMARY KEY (contrib_date,donor_name,committee_member));
The Code that is giving me errors:(not a group by expression):
CREATE or REPLACE VIEW adams_conrtibution_total as select program, committee_member, sum(amount) from contribution_list where committee_member = 'Adams' group by program;
I'm trying to find the easiest way to get a list of every data source on a RS instance, including the name of the SQL Server that the data source points to. We want to make sure none of the data sources point at our primary OLTP server.
I looked at the DataSource table in the ReportServer DB, but of course the connection string info is encrypted. I'm hoping that I don't need to resort to writing .net code to hit the web service.
I need copy one database from one server into another server. Both servers have SQLSERVER 2000. One way is copy the LDF files directly but I need to stop the engine to do that, I€™m sure there are many other ways more efficiently. I do need an advice. Thanks in advance
Often discussed, but not really solved in my opinion - the connection between the partners and the witness causes problems.
My case: Three Servers in the same domain, three endpoints on 5022 with windows negotiation, all endpoints can be reached by telnet from each server. Mirrorring works. So far so good.
But one of these partners is not able to connect to the witness. The witness' error log is full with that:
"2006-06-01 13:45:20.32 Logon Database Mirroring login attempt failed with error: 'Connection handshake failed. An OS call failed: (8009030c) 0x8009030c(Der Anmeldeversuch ist fehlgeschlagen.). State 67.'. [CLIENT: 130.143.205.54]"
My Endpoints are created like
CREATE ENDPOINT [EASYRIS_Mirroring]
AUTHORIZATION [code1dephbrsaa1-sys108]
STATE=STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = SUPPORTED ALGORITHM RC4);
What catches my eyes is that
GRANT CONNECT ON ENDPOINT::EASYRIS_Mirroring TO [code1dephbrsaa1-sys108];
doesn't cause these user to appear in the result set of
I want to run a SQL Agent step as a different user than the one used by the Agent service.  I thought that I would create a new credential and then have a proxy use it, however there are none for just plain old SQL.  This is a SQL 2014 Enterprise instance. The reason for this is that I the Windows login that is used by the Agent service is not defined as a login in the database, I don;t want to therefore add it in as a user with restrictive rights it case it impacts on other jobs.  What is the easiest way of have a single job step run under a different Login?Â
I have a table;CREATE TABLE theLiterals (theKey varchar (255) NOT NULL ,theValue varchar (255) NULL)INSERT INTO theLiterals VALUES('defaultServer','\MyServer')INSERT INTO theLiterals VALUES('defaultShare','MyShare')INSERT INTO theLiterals VALUES('defaultFolder','MyFolder')INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')I then try;SELECTdefaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue ENDFROM theLiteralsand I get;defaultServer defaultShare defaultFolder defaultFile\MyServer NULL NULL NULLNULL MyShare NULL NULLNULL NULL MyFolder NULLNULL NULL NULL MyFile.datbut I want it COALESCEd like this;defaultServer defaultShare defaultFolder defaultFile\MyServer MyShare MyFolder MyFile.dat....but my syntax is incorrect. Is there an efficient way of doing this.I want to have a script/UDF where I can say...GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')and then my one-row recordset will be...RS(0) will = '\MyServer'RS(1) will = 'MyShare'RS(2) will = 'MyFolder'RS(3) will = 'MyFile.dat'Thanks for any help!
I'm trying to gather information from within a SSIS package for benchmarking, reconciliation, and reporting purposes in regards to cube processing, which I'm initiating using the AS processing task.
What is the easiest way to capture this information?
The only way I've been able to come up with is to use a profiler trace. If this is really the only way, what is the easiest way to execute and read the trace from within SSIS?
Also, if a script task has to be used, does anyone have a code sample?
Hi, My package dumps the errors into a table. The problem is, it couldnt dump Error Output column to a varchar field. I have added an script component in between to transform to string but no success.
I tried ErrorOutput.GetBlobData(0, ErrorOutput.Length)
but when I query the database, it says "System.Byte[]'
I've tried creating a simple SSIS package but I get the error:
[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".
THis is a known bug and while we wait for SP1 to fix this error, is there another way to simply copy tables (with indexes and without having the identity column renumbered) ?
My error is that 'Name tr is not declared' tr.Rollback() I tried moving the 'Dim tr As SqlTransaction' outside the try but then I get 'Variable tr is used before it si assinged a value'. What is the correct way? Try conn.Open() Dim tr As SqlTransaction tr = conn.BeginTransaction() cmdInsert1.Transaction = tr cmdInsert1.ExecuteNonQuery() cmdInsert2.Transaction = tr cmdInsert2.ExecuteNonQuery() cmdInsert3.Transaction = tr cmdInsert3.ExecuteNonQuery() tr.Commit() Catch objException As SqlException tr.Rollback() Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next Finally conn.Close() End Try
I have the below procedure that will not work- I must be losing my mind, this is not that difficult - mental roadblock for me. Using SQL Server 2000 to create SP being called by ASP.Net with C# code behind stored procedure only returns if input exactly matches L_Name PROCEDURE newdawn.LinkNameLIKESearch @L_Name nvarchar(100)AS SELECT [L_Name], [L_ID], [C_ID], [L_Enabled], [L_Rank], [L_URL] FROM tblContractorLinkInfo WHERE L_Name LIKE @L_Name RETURN I tried: WHERE L_Name LIKE ' % L_Name % ' no luck. What am I missing? Thank you
SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle,C.CategoryTitle FROM HomePageImage H JOIN shortlist SL on H.StoryId = SL.id (INNER JOIN category C on H.CategoryId = C.CategoryId) order by date DESC
I have a textbox and a checkbox on a form and I'd like to add both values to a db. The textbox value gets inserted fine but I'm having trouble with the checkbox. Any ideas would be greatly appreciated.
Beside working right from the server how else someone can perform the SQL admistration job, I guess my question is how do most SQL DBA perform their administration without going to the server directly. Anyone --- can help please??
Declare @StartDate datetime Declare @EndDate Datetime Set @StartDate = dateadd(day, datediff(day, 0, getdate()), 0) Set @EndDate = getdate()
The job runs at 11:30 pm so I want the start date to be the same but the time to be equal to 00:00:00.0 When I run the getdate does it also return a time stamp?
I had a problem where some users were experiencing timeouts when trying to add a single record to a table with 2.3 million records. It's not a very wide table; only 10 columns and the biggest column in varchar 500. The rest are guid, datetime, tinyint...
There is also an old VB app that inserts about 3000 records a day into this table during office hours while users occasionally try and insert a record into this table.
Something said to me that the problem could be indexes but I wasn't quite sure because I though indexes only have an impact on select, delete & update. And not particulary on insert. But I checked it out anyway and noticed that the 3 indexes (1 column PK, 1 column Clustered & 1 column non-clustered) weren't padded. So I changed that (Fill Factor 95) and the problem has gone away. But why? I thought the insert would just have appended it to the end of the index before I made this change? Why would that time out?
I have the following queries. The first returns the 'Unknown' row, the second works the way I would expect. Are my expectations wrong? Can someone describe for me what is going on?
Thanks
Code Snippet select * FROM SynonymComFinancialCategory b LEFT JOIN TT_FinancialCategory a ON a.FinanceGroup = b.FinanceGroup AND a.FinanceCode = b.FinanceCode AND a.Finance = b.Finance AND b.Finance <> 'Unknown' WHERE a.FinanceGroup IS NULL AND a.FinanceCode IS NULL AND a.Finance IS NULL
Code Snippet select * FROM SynonymComFinancialCategory b LEFT JOIN TT_FinancialCategory a ON a.FinanceGroup = b.FinanceGroup AND a.FinanceCode = b.FinanceCode AND a.Finance = b.Finance WHERE a.FinanceGroup IS NULL AND a.FinanceCode IS NULL AND a.Finance IS NULL AND b.Finance <> 'Unknown'
Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile Business PrivateNULL 345 NULL4646 65464 65765NULL 564654654 564 6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks
