Query :
select * from Item1 a (NOLOCK, Index(ItemIn1))
join Item2 b (NOLOCK, Index(ItemData1))
Please explain the above nolock etc after the alias in the given query. What it does ?
Sam.
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
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
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
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?
Thanks!
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) 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.
Your input will be appreciated
Thank you
I was trying to figure out what the OPENQUERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
View 7 Replies View RelatedHi All,
In SQL Studio, there is a really handy option to parse your query prior to running it (that little green tick button next to Execute).
I dont suppose there is a similar thing that can be used on the command line at all is there??
Cheers
-
Karym6
Hi,
Following is my one of the field of Server table and it s data (SQL Server 2000 Desktop Ver.)
WORK_DESC
G/B SHADE
VALVE R/C
R/C
R/C ISU IND
CP FLANGE R/C
R/C
FAB. KICK LINE
COAT R/C LINE
R/C
To get the specific data of this WORK_DESC from table, I used following query which results fine.
SELECT RpoMstId, RPO_No, Start_Date, WORK_DESC
FROM T_RPO_Master
WHERE (WORK_DESC = N'R/C')
Which results:
R/C
R/C
R/C
What would be the syntax if I need to represent the data that has ‘R/C’ prefix or suffix in the data field? (anywhere in the data column)
With kind regards,
Ashfaque
This runs in Access, but SQL Server 7 complains that BETWEEN is unrecognized. Can anyone help me? thanks
SELECT yearId, IIf(Date() BETWEEN [qrtOneStart] AND [qrtOneEnd],1, IIf(Date() BETWEEN [qrtTwoStart] AND [qrtTwoEnd], 2, IIf(Date() BETWEEN [qrtThreeStart] AND [qrtThreeEnd], 3, 4))) AS CurrentQrt, yearName
FROM tblYear
hi anybody know what is meaning of "Syntax error converting datetime from character string" is data entry problem, tq
View 5 Replies View RelatedHi,I have an issue with my query.1. I have 1 stored proc which have execution calls to multiple storedprocs within it.2. I want to wrap that main stored proc in the transaction and rollbackif there are errors execution calls to other stored procs. I don'tbelieve my code is accounting for errors occuring in the executionstatement to other stored proc.3. Is there an easy way to do this without creating tranaction on eachstored proc and returning the error code? How do I make this happen?Below is the code.Thanks:DALTER procedure spAG_Add_Product@prodCostmoney,@prodWeightdecimal,@prodDescnvarchar(100),@prodNamenvarchar(50),@prodSizenvarchar(100),@pic_filenamenvarchar(50),@userIdint,@exhib_idintASdeclare @auth_Logic_idintdeclare @intErrorCodeintBEGIN TRANSELECT @auth_Logic_id= AG_Auth_Logic.Auth_Logic_ID FROMAG_Auth_LogicINNER JOIN AG_Base_Active_State ON AG_Auth_Logic.Base_Active_State_ID= AG_Base_Active_State.Base_Active_State_IDWHERE AG_Auth_Logic.Action_Description LIKE N'%category%'AND AG_Base_Active_State.Is_Alive = 1INSERT INTO AG_Individual_Product(Product_cost,Product_weight,Product_description,Product_name,User_ID,Auth_Logic_Id,Product_size)VALUES (@prodCost,@prodWeight,@prodDesc,@prodName,@userId,@auth_Logic_id,@prodSize)declare @prod_idintselect @prod_id = Scope_identity()-- add to pic tabledeclare @pic_id_outintexec spAG_Add_Picture @pic_filename,@prodName, @pic_id = @pic_id_outoutputdeclare @prod_pic_outint-- add to product_pic tableexec spAG_Add_Product_Picture @pic_id_out,@prod_id, @prod_pic_id = @prod_pic_out output-- add to product_pic_in_exhibexec spAG_Add_Product_Picture_in_Exhibition @prod_pic_out,@exhib_id, @prod_idselect @prod_idSELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEMCOMMIT TRANPROBLEM:IF (@intErrorCode <> 0) BEGINROLLBACK TRANEND
View 1 Replies View RelatedI am not sure why this is producing a SQL Server related error, but w/ohaving an instance of SQL Server on my machine to verify anything further,can you all help me with this?<!--- validate() ---><cffunction name="validate" access="remote" returnType="numeric"><cfargument name="username" required="yes" type="string" /><cfargument name="password" required="yes" type="string" /><cfquery name="validate" datasource="#request.dsn#">SELECT userIDFROM UserWHERE username = '#UCase(username)#'AND password = '#UCase(password)#'</cfquery><cfif validate.RecordCount EQ 0><cfquery name="log" datasource="#request.dsn#">UPDATE UserSET lastLoggedIn = #createOdbcDate(now())#WHERE userId = #validate.userID#</cfquery><cfreturn validate.userID /><cfelse><cfreturn 0 /></cfif></cffunction>Sorry that's all I can honestly provide, other than the error being on theline with <cfif validate.RecordCount>Phil
View 2 Replies View RelatedHi,I'm new to ASP and have stumbled across what appears to be a commonproblem, but after trying several solutions from other posts I've hadno luck. My SQL SELECT statement is fine elsewhere (e.g. in ACCESS),but when executed from ASP I get this syntax error:Error Type:Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrectsyntax near '16'./forecast/test.asp, line 27(line 27 is where the date is in the HAVING part of the statement)Here is the Statement:----------------------SELECT dbo_ForecastIn.DataTime AS E_Time, dbo_ForecastInData.DataTimeAS FD_Time, dbo_ForecastInData.WindFarmPower AS ForecastPower FROMdbo_ForecastIn INNER JOIN dbo_ForecastInData ON dbo_ForecastIn.DataID= dbo_ForecastInData.DataID GROUP BY dbo_ForecastIn.DataTime,dbo_ForecastInData.DataTime, dbo_ForecastInData.WindFarmPower HAVINGdbo_ForecastIn.DataTime = #06/02/2004 16:00:00# ORDER BYdbo_ForecastIn.DataTime, dbo_ForecastInData.DataTime;Here is the code:-----------------SQLStmt = "SELECT dbo_ForecastIn.DataTime AS E_Time,dbo_ForecastInData.DataTime AS FD_Time,dbo_ForecastInData.WindFarmPower AS ForecastPower "SQLStmt = SQLStmt & "FROM dbo_ForecastIn INNER JOINdbo_ForecastInData ON dbo_ForecastIn.DataID =dbo_ForecastInData.DataID "SQLStmt = SQLStmt & "GROUP BY dbo_ForecastIn.DataTime,dbo_ForecastInData.DataTime, dbo_ForecastInData.WindFarmPower "SQLStmt = SQLStmt & "HAVING dbo_ForecastIn.DataTime = #06/02/200416:00:00# "SQLStmt = SQLStmt & "ORDER BY dbo_ForecastIn.DataTime,dbo_ForecastInData.DataTime;"response.write(SQLStmt)Set RS = Connection.Execute(SQLStmt)Hope one of you geniuses can sort me out.Thanks.
View 1 Replies View RelatedHi,
I've been tasked with investigating how we can migrate from oracle to sql server. I have successfully converted a typical schema with 97% success using SSMA and have a query regarding ADO and oracle syntax.
I have heard that the SQL server provider can interpret oracle syntax into its own syntax, but i am unable to find any reference/examples on the web. Is this possible? and if so could any kind soul please post some links.
Thanks in advance,
Michael
declare @b nvarchar(1)
set @b = '1'
if(@b='1')
begin
select * into #example from example
select * from #example
drop table #example
end
else
begin
select * into #example from example
select * from #example
drop table #example
end
With syntax check, I always get "'#example already exist'"
But why? Just because of two "select into temp table" operation?
I am confused.
Thank you.
Hi guys,
I need to pass some SQL to someone else who will run it on their database. I have got the SQL for SQL Server 2000 but they are running SQL Server 7. Apparently the below MSSQL 2000 script doesn't work;
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tableName](
[id] [int] IDENTITY(1,1) NOT NULL,
[ArticleID] [int] NULL,
[Heading] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BodyContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WrittenDate] [datetime] NULL,
CONSTRAINT [tableName] PRIMARY KEY CLUSTERED ( [id] ASC )
)
What is the equivalent of the above in for SQL Server 7? I don't have access to it via SQL Server Manager so have to run the script.
I am confused. Any help you can provide, to resolve this error, would be very appreciated...... Thanks in advance!!
I am getting this error message......
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.
/formstoday/issue_log-preview.asp, line 76
The code for line 76 is thus......
objConn.execute sql
Here is the code gor the asp page........
<%
Dim StrEmail
Dim StrName
Dim StrIssue
StrEmail=request.form("email")
StrName=request.form("name")
StrIssue=request.form("issue")
Set objConn = Server.CreateObject("ADODB.Connection")
Set rs = server.createobject("ADODB.Recordset")
objConn.open "Driver={SQL server};Server=document;DSN=SQL;Database=issues;UI D=steu;PWD;"
sql = "Insert into issue-log2 (name,email,issue) values('" & _
strName & "','" & _
strEmail & "','" & _
StrIssue & "')"
objConn.execute sql
%>
Steu
Dear All,
My VB.Net application connects to MSSQL. It is always running fine for a few days, but encounters "incorrect syntax" as following unless the server is restarted.
----
Exception occurred System.Runtime.InteropServices.COMException (0x80040E14): Line 1: incorrect syntax near 'CDO'.
at ADODB.ConnectionClass.Execute (String CommandText, Object& RecordsAffected, Int32 Options)
----
There are a few applications in the server. If certain service is stopped, my program continues to run. So I am sure that the MSSQL connections have been taken up, which causes the error. How to prove it? And is there any way to reserve some DB connections to a particular application only?
Thanks for any hint!
I attach my codes below. Anything wrong with the way that I handled the ADODB?
------------------------------------------------------------
Public Sub SendAllEmails()
Try
Dim cn As ADODB.Connection = openConn()
Dim rs As ADODB._Recordset
Dim rs2 As ADODB._Recordset
sqlstmt = "select * FROM EMAILTABLE"
rs = cn.Execute(sqlstmt)
While Not rs.EOF
Dim MAIL_ADD_USED As String = rs.Fields("MAIL_ADD_USED").Value.ToString
sqlstmt = "select * from NAMETABLE where EMAIL = '" & MAIL_ADD & "'"
rs2 = cn.Execute(sqlstmt)
If Not rs2.EOF And ErrMsg = "" Then
ErrMsg = SendMail(MAIL_ADD, REPORT_TITLE)
If Not ErrMsg Is Nothing And ErrMsg.Equals("Success") Then
MAIL_DATE_SENT = Now.ToString
MAIL_STATUS = "S"
'wait to make sure the email is sent
System.Threading.Thread.Sleep(1000 * 30)
Else
MAIL_STATUS = "F"
End If
End If
rs2.Close()
sqlstmt = "update EMAILTABLE set " & _
" MAIL_STATUS = " & MAIL_STATUS & "," & _
" MAIL_ERRMSG = null" & _
" where EMAIL = " & MAIL_ADD
cn.Execute(sqlstmt)
rs.MoveNext()
End While
rs.Close()
cn.Close()
Catch e As Exception
EventLog1.WriteEntry("Exception: " & e.ToString)
End Try
End Sub
----------------------------------------------------------------------
What is wrong with my syntax?I want to return the value of the AchiveYear Value based on records in theCall that match.
SELECT DATEPART(yyyy,Call_Date) AS ArchiverYear
FROM tblCall
INNER JOIN PrismDataArchive.dbo.ArchiveDriver AS Arch ON tblCall.DATEPART(yyyy,Call_Date) = Arch.ArchiveYear
[code]...
Hi All,
I need some help from u..
I wanted to know the exact flow behind SQL Server when we fire
SELECT select_list
[ FROM table_source ]
[ON Join_Condition]
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
what is exact execution process mean which get first strike to server and then what followed then..
T.I.A
DESCRIBE in Oracle = ? in SQL SERVER?
Does anyone have a matrix of Oracle to SQL Server common commands?
I am having trouble accessing my company's hosted SQL Server manuals.
TIA
JEJ
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
View 4 Replies View RelatedI know how to access different tables on the same server by prefixingthe table with the database name. Is there anyway to prefix theserver name to link two tables across servers? Thanks.
View 2 Replies View RelatedHi everyone, is there any way to turn off SQL server re-writing the syntax of certain queries? An example would be if in the where you set primary keys equal to foreign keys and then it converts it to inner-joins. Thanks for your help.
Jon
I am trying to do the DTS export from SQL server 2000 to spreadsheet.Every single time i am getting the error [Microsoft][ODBC SQL Server Driver][SQL server]Syntax error converting the nvarchar value '1version=9' to a column of datatype int.it retrieved some row,i can see that in preview but it failed at some row number..So to see the exact row or where its stopping i ran the query under view (Databases->DBname->New->View)it retrieved some row and stopped at row with custid 1947 .there are 50000 rows in a table..i changed the query by adding 'AND (dbo.Customer.CustomerId > 1947)'..i changed the number 1947 to other numbers too..SELECT dbo.Customer.CustomerId, dbo.Customer.Email, dbo.ShippingAddress.CustomerId , dbo.ShippingAddress.CountryFROM dbo.Customer INNER JOIN dbo.ShippingAddress ON dbo.Customer.CustomerId = dbo.ShippingAddress.CustomerIdWHERE (dbo.ShippingAddress.Country = 1) AND (dbo.Customer.CustomerId > 1947)i don't see any problem with the rows after 1947 (but obiviously i am missing to notice something!)..but i keep getting this error and i am not able to proceed at all.Is there any kind of error log i can see??..i am running it on server and not on my local machine.I would highly appreciate any guidance regarding this.thanks,
View 3 Replies View RelatedThis code used to work now I get an error(error follows code) can anyone tell me where I went wrong?[vbcode]i = 0 For i = 0 To 7 If requests(i) <> "" Then request = requests(i) Dim sql1 As String = "insert request_items ([req_id],[request]) Select Case max(req_id), " & request & " from(requests)" Dim myCommand1 As New SqlCommand(sql1, myConnection) myCommand1.ExecuteNonQuery() Else Continue For End If Next[/vbcode] Server Error in '/' Application.
Incorrect syntax near ','.
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.Data.SqlClient.SqlException: Incorrect syntax near ','.
Source Error:
Line 73: Dim sql1 As String = "insert request_items ([req_id],[request]) Select Case max(req_id), " & request & " from(requests)"Line 74: Dim myCommand1 As New SqlCommand(sql1, myConnection)Line 75: myCommand1.ExecuteNonQuery()Line 76: ElseLine 77: Continue For
Source File: C:InetpubloansMemberPagesRequest.aspx.vb Line: 75
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near ','.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 Request.Button1_Click(Object sender, EventArgs e) in C:InetpubloansMemberPagesRequest.aspx.vb:75 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
I wonder whether there's documentation about inline syntax for indexes for SQL Server 2014?
CREATE TABLE Consumer
(
Account nvarchar(20) null,
Consumption float null,
INDEX IX_Consumer_Account NONCLUSTURED (Account)
);
Could someone give me the general syntax to pull xml data from a web server that I have access to? I do know that I can not use an ip address, I have to use the hostname, etc.
I was using something like
FROM OPENROWSET(BULK '\SERVERNAMESHAREPATHDATAFILE.XML', SINGLE_BLOB) as x
instead of using SERVERNAME I am user http:\www.mycompanyname.commycompanynamestoredata.xml
I do not understand why this does not work. I hope I gave enough information for this.
Please help.
Thank you
LadyDee
Hi,
I'm using ComponentArt's Callback grids with Manual Paging.
The CA example grid uses Access:(http://www.componentart.com/webui/demos/demos_control-specific/grid/programming/manual_paging/WebForm1.aspx)
That SQL syntax produced is invalid in SQL Server 2005.
Example:
"SELECT TOP " & Grid1.PageSize & " * FROM (SELECT TOP " & ((Grid1.CurrentPageIndex + 1) * Grid1.PageSize) & " * FROM Posts ORDER BY " & sSortColumn & " " & sSortOrderRev & ", " & sKeyColumn & " " & sSortOrderRev & ") ORDER BY " & sSortColumn & " " & sSortOrder & ", " & sKeyColumn & " " & sSortOrder
So...This is what I have (simplified), and it appears return incorrect rows on the last few pages:
SELECT top 15 * FROM Posts where & sFilterString & " and Postid in (SELECT TOP " & ((Grid1.CurrentPageIndex + 1) * Grid1.PageSize) & " Postid FROM Posts where " & sFilterString & " ORDER BY " & sSortColumn & " " & sSortOrder & ") " & " ORDER BY " & sSortColumn & " " & sSortOrderRev
What other approaches has anyone used besides the "ID in (...)"?The examples I have included show the available variables: sort asc and desc, current page, number of rows on a page, etc.
I am trying to get a SQL statement to work with both Access 2000 and SQL Server 2000.
The statement that works in SQL Server is:
---
UPDATE [myTable2]
SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0
FROM [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]
WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---
(names have been changed to protect the innocent)
The statement that works in Access is:
---
UPDATE [myTable1] INNER JOIN
(myTable2 INNER JOIN [myTable3]
ON [myTable2].[FieldX]=[myTable2].[FieldY])
ON [myTable1].[FieldZ]=[myTable2].[FieldY]
SET
[myTable2].[FieldA] = 'Hello',
[myTable2].[FieldB] = 2,
[myTable2].[FieldC] = 'xxx',
[myTable2].[FieldD] = 0
WHERE ([myTable2].[FieldY]=1)
And ([myTable3].[FieldZ]='xxx');
---
It seems that neither will accept the other format. Can anyone suggest how I can rearrange the statement so that it works in both?
I'm getting an error as "Incorrect syntax near the keyword 'CASE' ". When trying to run this query.
CREATE PROCEDURE dbo.SP_CDB_EA2
@RiskRef varchar(100)
--drop table #Tmp
AS
BEGIN
[code]....
Hi,
I using "Microsoft SQL Server 9.00.3042.00" at home. But our company old system using "Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) ". I think therefore i showing this error when running.
Query:
SELECT @@VERSION
Results:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
-------------------------------------------------------------------------------------------------------------------
Query:
GO
CREATE VIEW dbo._UrunAgaci_KullanimMiktarlari
AS
WITH RPL (AnaUrunKod, BilesenKod, BilesenMiktar) AS
(
SELECT ROOT.AnaUrunKod, ROOT.BilesenKod, ROOT.BilesenMiktar
FROM _UrunBilesenListesi ROOT
/* WHERE ROOT.AnaUrunKod = '100.0.622' */
UNION ALL
SELECT PARENT.AnaUrunKod, CHILD.BilesenKod, PARENT.BilesenMiktar*CHILD.BilesenMiktar
FROM RPL PARENT, _UrunBilesenListesi CHILD
WHERE PARENT.BilesenKod = CHILD.AnaUrunKod
)
SELECT AnaUrunKod, BilesenKod, SUM(BilesenMiktar) AS [TopÄ°ht]
FROM RPL
GROUP BY AnaUrunKod, BilesenKod;
GO
Results:
Server: Msg 156, Level 15, State 1, Procedure _UrunAgaci_KullanimMiktarlari, Line 3
Incorrect syntax near the keyword 'WITH'.
-------------------------------------------------------------------------------------------------------------------
What is solving?
Thanks
How to alter all objects in database i want to find if can any syntax errors in my database after restoring from sql 2008 to 2012. I Can create as test and drop them but trying to find a way to alter proc , views and functions..
View 4 Replies View Related