Hello
I have having trouble displaying some simple columns in ascending order.
I know that the database is populated and I can get the more complex code to work if I display like this:
SELECT FName, LName, Town, '<a href="' + url + '">' + Site + '</a>' as Link
FROM Names_DB
WHERE FName = 'Tom'
And url like 'http:%'
ORDER BY LName ASC
But I need a simpler view but I can't get it to work
I have tried this:
SELECT FName, LName
FROM Names_DB
ORDER BY LName ASC
And this
SELECT FName, LName
FROM Names_DB
ORDER BY LName ASC;
And This:
SELECT FName, LName
FROM Names_DB
ORDER BY LName 'ASC'
What is wrong with this syntax?
Thanks
Lynn
I have a query and I need to check to see if a field is occupied, i.e., it can have anything in it, i just want to see if something is there... this is what I want, but of course, anything isn't the right word here...
This is a Stored proc I am working on I dont know much about stored procs. This is what I am trying to achieve The proc takes two input parameter and returns one outparameter.
This is what I have
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[GetNextAction]
( @Code char(10), @Track varchar(30), @NextAction ntext output ) AS BEGIN
SET NOCOUNT ON; Declare @ID int; @ID = Select Sequence from [dbo].[Track] where Code=@Code; Declare @nextCode varchar; @nextCode= Select Code from [dbo].[Track] where sequence =(@ID+1); @NextAction= Select nextAction from [dbo].[CaseStage] where Code=@nextCode; return @NextAction;
END GO
I guess you can understand what I am trying in the proc but the rules and syntax are wrong. Please help me with this. Thanks
If someone can show me the correct syntax for the right side of the code below, I'd appreciate it. srcMoreInfo.InsertParameters("Address").DefaultValue = DirectCast(cuwMoreInfo.FindControl("txtAddress"), TextBox).Text
WHATS WRONG IN THIS CODE ? --------------------------------- PS: I'm getting this error: -------------------------------
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 7 Cannot convert parameter '@statement' to ntext/nchar/nvarchar data type expected by procedure.
Hi, I'm new to SQL and was wondering if there was an easier way to filter data.
I have two tables -
The first table called Names of Companies has a column named: NAMES
NAMES XYZ Company ABC Limited Liability Company ZZZ Corporation KKK Inc. ABC Inc.
The second table called Keywords has a column named: WORDS
WORDS Company Limited
I want to search for all NAMES that contain the WORDS in some form
The results should be:
NAMES XYZ Company ABC Limited Liability Company
Technically, I can get the results I want by manually typing into the SQL statement all the words that appear in the WORDS column.
SELECT * FROM [Names of Companies] WHERE [Names of Companies].Names Like "*Company*" Or ([Names of Companies].Names) Like "*limited*"));
But is there a way that I reference the table Keyword instead of typing into the query statement all the words that appear in the column WORD? I have a lot of words to search for.
I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.Here's a snippet from the stored procedure that creates the job:CREATE PROCEDURE [dbo].[spArchive] @DB varchar(30), @Date DateTimeAS EXEC msdb.dbo.sp_add_jobstep @job_name = 'ArchiveIncentives' , @step_id = 1 , @step_name = 'ArchiveAHD' , @subsystem = 'TSQL' , @command = 'spArchiveAHD ''@Date''' , @on_success_action = 3 , @on_fail_action = 2 , @database_name = '@DB' , @retry_attempts = 1 In this case, the job will be calling this stored procedure:CREATE PROCEDURE [dbo].[spArchiveAHD] ( @dtArchiveBefore DateTime)AS I'm unclear about these lines: @command = 'spArchiveAHD ''@Date''' @database_name = '@DB' Do they look correct to you or should I drop some/all of the apostrophes?Robert W.
I try to call the storeproc to perform task by allowing only input 6 of length. The syntax over here might not be relevant, im seeking for correct syntax. webform Dim connString2 As String = _ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString Using myConnection2 As New SqlConnection(connString2) Dim test01 As IntegerDim myPuzzleCmd2 As New SqlCommand("GetRandomCode", myConnection2) myPuzzleCmd2.CommandType = CommandType.StoredProcedure Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, , 6) < -------- allow 6 letters in length to be input retLengthParam.Direction = ParameterDirection.InputmyPuzzleCmd2.Parameters.Add(retLengthParam) Dim retRandomCode As New SqlParameter("@RandomCode", SqlDbType.VarChar, 30) retRandomCode.Direction = ParameterDirection.Output myPuzzleCmd2.Parameters.Add(retRandomCode) TryDim reader2 As SqlDataReader = myPuzzleCmd2.ExecuteReader() myPuzzleCmd2.ExecuteNonQuery() Catch ex As Exception Response.Write("sp value : " & retRandomCode.Value) Dim iRandomCode(1) As StringReDim Preserve iRandomCode(1) iRandomCode(1) = Convert.ToString(retRandomCode.Value)myPuzzleCmd2 = Nothing Session.Remove("RandomCode") HttpContext.Current.Session("RandomCode") = iRandomCode(1) Response.Write(Session("RandomCode")) Finally myConnection2.Close() End Try End Using
This (demo) statement is fine in Access, and so far as I can see, shouldbe OK in SQL Server.But Enterprise Manager barfs at the final bracket. Can anyone helpplease?select sum(field1) as sum1, sum(field2) as sum2 from(SELECT * from test where id < 3unionSELECT * from test where id 2)In fact, I can reduce it to :-select * from(SELECT * from test)with the same effect - clearly I just need telling :-)cheers,Jim--Jima Yorkshire polymoth
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
I have a test page where I'm using SqlConnection and SqlCommand to update a simple database table (decrease a number). I'm trying to figure out how to make a number in the database table to decrease by 1 each time a button is being pressed. I know how to update the number by whatever I want to, but I have no idea what the correct syntax is for putting variables inside the query etc. Like "count -1" for instance. The database table is called "friday" and the one and only column is called "Tickets". Here's the code behind the button:protected void Button1_Click(object sender, EventArgs e) {SqlConnection conn; conn = new SqlConnection("Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***"); conn.Open();int count = -1; SqlCommand cmd = new SqlCommand("select Tickets from friday", conn); count = (int)cmd.ExecuteScalar();if (count > 0) { string updateString = @" update friday set Tickets = 500" ; <------ Here I want to set Tickets like "current count -1"SqlCommand cmd2 = new SqlCommand(updateString); cmd2.Connection = conn; cmd2.ExecuteNonQuery(); } else { } conn.Close(); }
I am having trouble finding the correct syntax to access a variable. I have a variable defined in the Variables window: The variable name is formatedDate. The DataType is String.
I am successfully setting the value of the variable in a Execute SQL Task. The SQL is as follows:
SELECT LEFT(CONVERT(VARCHAR, MAX(ReportDate), 120), 10) as formatedDate from DimReportDates
The Result Set is set to “Single Row” and properly set up.
No problem so far. I can see with a watch that the variable has the correct value, something like:
‘2015-09-30’
Now, in a subsequent step, a Data Flow Task, I want to access the variable. Actualy it is in the SQL statement of a OLE DB source in the Data Flow… I have the following:
Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!
I'm looking for the correct syntax to pull back duplicate vendors based on 6 fields from two different tables. I want to actually see the duplicate vendor information (not just a count). I am able to pull this for one of the tables, something like below:
select * from VendTable1 a join ( select firstname, lastname from VendTable1 group by firstname, lastname having count(*) > 1 ) b on a.firstname = b.firstname and a.lastname = b.lastname
I'm running into issues when trying to add the other table with the 4 other fields.
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring")) Dim cmdSQL As New SqlCommand(strSQL, myConnection)
Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
) ) ) ) ) AS timeType, Sum([2007_hours].Hours) AS SumOfHours from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql): select ID, FName, LName if(SUBSTRING(FirstName, 1, 4)= 'Mike') Begin Replace(FirstNam,'Mike','MikeTest') if(SUBSTRING(LastName, 1, 4)= 'Kong') Begin Replace(LastNam,'Kong,'KongTest') if(SUBSTRING(Address, 1, 4)= '1245') Begin ......... End End
end
Case Statement might be the solution but i could not do it.
I've created C#.net program (behind code style). when I run it in Internet explorer, the following error occurs in IE window. pls instruct me how to handle and correct this error. And how to initialize the connectionstring... Great thank!
Server Error in '/' Application. -------------------------------------------------------------------------------- The ConnectionString property has not been initialized. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: The ConnectionString property has not been initialized. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [InvalidOperationException: The ConnectionString property has not been initialized.] System.Data.SqlClient.SqlConnection.Open() +809 CodeBox.BehindCode.getSubject() +80 CodeBox.BehindCode.Page_Load(Object sender, EventArgs e) +31 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.0.3705.0; ASP.NET Version:1.0.3705.0
I have a simple while process to use with a trigger to insert values into another table. IN VB this was simple but the while in TSQL seems a little different. If anyone can point out my flaw greatly appreciated.
while @cnter < @nodays --insert values insert into table values (value1, value2) --then increment counters and repeat set @sdate = @sdate + 1 Set @cnter = @cnter + 1 How or what is the best way to loop back?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
If you start receiving continuous error messages by e-mail indicating that the transaction log is full. After 2 days, the messages suddenly stopped. What could be the reason?
Windows NT App log is full or SQL Server Agent stopped
I think SQL Server Agent stopped How do you think..and why ?
Im new to SQL so please bear with me & help me as to why Im not getting the desired results.
I want to find the difference between two sets of tables that reside in different databases but contain the same data. I ONLY WANT a. records that are only in A but not in B b. records that are only in B but not in A ______________________________________________________________________
Here is what I wrote using something that I found in this forum -
CREATE PROCEDURE RPT_DETAILS AS BEGIN DECLARE @Rowcount AS INT DECLARE @First_Name AS VARCHAR(50) DECLARE @Last_Name AS VARCHAR(50) DECLARE @Id AS INT
CREATE TABLE #Prowess(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50)) CREATE TABLE #SDK(ID INT NOT NULL, First_Name VARCHAR(50), Last_Name VARCHAR(50))
INSERT INTO #Prowess SELECT bb.beenumber, be.FirstName, be.LastName FROM beebusiness bb join beeentity be on bb.beebusinessguid = bb.beebusinessguid
INSERT INTO #SDK SELECT cast(sa_ss as INT), first_name, last_name from ml
SELECT @ROWCOUNT = MAX(ID) FROM #SDK PRINT '------------------------------------------------------------------------------------------' PRINT '------------------------COMPARISION REPORT Between Prowess & SDK--------------------------' PRINT '------------------------------------------------------------------------------------------'
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Prowess WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
SELECT @ROWCOUNT = MAX(ID) FROM #Sdk
PRINT 'TOTAL Difference ('+ + CAST(@ROWCOUNT AS VARCHAR(50))
WHILE @ROWCOUNT > 0 BEGIN SELECT @First_Name = First_name, @Last_Name = Last_name, @ID = ID FROM #Sdk WHERE ID = @ROWCOUNT PRINT ' * '+@First_Name+@Last_Name SET @ROWCOUNT = @ROWCOUNT - 1 END
declare @var varchar(50) set @var= 'COLUMNNAME' select ID, a.@var , b.@var from rooper a join jim_rooper b on b.id = a.id join b_rooper bb on bb.id = a.id where a.@var != b.@var
All that Im trying to do here is instead of using a columnname, Im trying to substitute it with a variable so that it can be referenced at multiple places...
