I have seen developer confused many times when they receive following error message.
Msg 325, Level 15, State 1, Line 7
Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.
The reason for this error is when user is tring to attempt to run query or procedure or logic which is not compatible with previous version of the SQL Server. When SQL Server 2000 is upgraded to SQL Server 2005 or SQL Server 2008, the database object compatibility should be also upgraded to next version. When database compatibility is set to previous version and they are attempted with procedure of newer version they will throw above error.
Fix/Workaround/Solution:
Change the database compatibility level using following command.
For SQL Server 2005:
EXEC sp_dbcmptlevel 'DatabaseName', 90
For SQL Server 2008:
EXEC sp_dbcmptlevel 'DatabaseName', 100
Reference : Pinal Dave (http://blog.SQLAuthority.com)
How would I or can I run this command on SSE (Sequel Server Express)?
Hi, Can anyone find the error of this query
SELECT ‘TNGModuleTNGExternal’ AS ‘Table’
GO
IF NOT EXISTS (SELECT name FROM syscolumns WHERE name=’cmt’ AND id=object_id(‘TNGModuleTNGExternal’))
EXEC sp_rename ‘[spider3].[TNGModuleTNGExternal].[comments]‘, ‘cmt’, ‘COLUMN’
GO
Hello Pinal,
I am .net developer and I am having error while updating and inserting text data into sql server.
The error occurs when the text contains character ” ‘ “. Which is normal when we insert a long paragraph that has many apostrophe’s s symbol.
I get an error as invalid symbol near s
You need to double the single quotes
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
Can you help me please?
anybody correct this syntax for me plz ?
CREATE DATABASE 111 ON ( NAME = 111_data,
FILENAME =’D:\Project 2009\Web-Modules\111\db\Data_File.mdf’,
SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )
LOG ON ( NAME = ’111_log’,
FILENAME = ‘D:\Project 2009\Web-Modules\111\db\Log_File.ldf’,
SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB )
when i run it, there is a error Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ’111′. w
Hi Neven,
If the initial character of any identifier name is a number then cover it within square brackets. So change the name of database to [111].
Regards,
Pinal Dave
Ok, heres one.
SQL Server 2008, a new install.
This query errors
SELECT ID
FROM User
This query runs
SELECT ID
FROM [User]
Settings???
Hello Steve,
USER is a keyword and SQL Server take it as a keyword. While parsing the statement it founds the keyword at wrong place (FROM clause) and returns error. When we write [USER], it takes it as an identifier.
Regards,
Pinal Dave
Hi Pinal,
Would you please help me fixing this error.
“(2 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’.
(1 row(s) affected)
(1 row(s) affected)”
The Store proc is as follows
——————————————————
Create Database Bank
Go
Use Bank
go
Create table Account([ID] int, Name Varchar(100),
AccountNo varchar(100), Balance money)
Go
Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())
Go
Create procedure usp_ImportMultipleFiles @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query =’master.dbo.xp_cmdshell “dir ‘+@filepath+@pattern +’ /b”‘
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
–print @max1
–print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "\n")'
–print @query
exec (@query)
insert into logtable (query) select @query
end
drop table #y
Executing the proc—-
Exec usp_ImportMultipleFiles 'c:\myimport\', '*.csv', 'Account'
—————————————–
Hi,
I am having kind of similar issue at my client. They are runnign their database on Microsoft Dynamics NAV with SQL server option. But when user tries to enter a sales order in NAV they get the following SQL error –
102,”42000″[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘{‘.
they are on SQL server 2008 and Microsoft dynamics NAV 4SP1 with NAV5.0 SP1 executables.
Thanks
Meenakshi
SELECT TOP (100) PERCENT b.bill_no, b.date, b.Amount, c.bill_no AS
Expr3, c.current_dat, em.date_adv_pay, em.adv_pay, ex.Date_of_expense,ex.Amount AS Expr5, ep.Date_of_Expense AS Expr6,
ep.Amount AS Expr7, en.bill_no AS Expr1,en.date AS Expr2, en.total, c.total AS Expr4
FROM bill_receive_entry b,
cus_pay_detail c,
emp_pay em,
expense_personnel ex,
expense_prod ep,
enlarge en
where b.date(+)=c.current_dat
and em.date_adv_pay=c.current_dat(+)
and ex.Date_of_expense = em.date_adv_pay(+)
and ep.Date_of_Expense = ex.Date_of_expense (+)
and en.date = ep.Date_of_Expense(+)
ORDER BY c.current_dat, c.bill_no, b.bill_no, em.date_adv_pay,ex.Date_of_expense, ep.Date_of_Expense, en.date
But the error is coming like, I am not able to detect the prob. Actually I am a fresher in sql. so plz do help it,s urgnt
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ‘)’.
It’s urgnt,
client is over-head!!!!!!!!!!
Is this website for demo only!!!!!!!!
what are u doing yaar
getting this error can u fix it ?
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 53: string del = “delete from tbluttam_allimage where id=” + DataList1.DataKeys[e.Item.ItemIndex];
Line 54: SqlCommand cmd = new SqlCommand(del, con);
Line 55: cmd.ExecuteNonQuery();
Line 56: heat();
Line 57:
Source File: c:\inetpub\vhosts\uttamindustries.com\httpdocs\admin_uttam\manageimage.aspx.cs Line: 55
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near '='.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1948826
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4844747
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +317
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
admin_uttam_manageimage.DataList1_DeleteCommand(Object source, DataListCommandEventArgs e) in c:\inetpub\vhosts\uttamindustries.com\httpdocs\admin_uttam\manageimage.aspx.cs:55
System.Web.UI.WebControls.DataList.OnDeleteCommand(DataListCommandEventArgs e) +108
System.Web.UI.WebControls.DataList.OnBubbleEvent(Object source, EventArgs e) +8736452
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.DataListItem.OnBubbleEvent(Object source, EventArgs e) +123
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565
when I run command below
BACKUP DATABASE 1310_dB
TO DISK = ‘C:\Backuprestore\1310_db1.bak’;
WITH FORMAT;
GO
I am getting message as
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ’1310′.
Please provide solution also let me know how to restore back up from file or file group
BACKUP DATABASE [1310_dB]
TO DISK = ‘C:\Backuprestore\1310_db1.bak’;
WITH FORMAT;
It is not a good practice to name an object name starting with a number
Hi Pinal,
Thanks for your help. Whenever I face any issue with Sql server and see your website, I will feel relaxed( I am sure I got the solution)
Thanks again
Vijay
Great,
It worked for me. I was trying to create a UDA using SQL clr and I was getting the exact same error and after executing the “EXEC sp_dbcmptlevel ‘db_name’, 100 ” I was able to deploy the UDA in VS successfully.
Thanks Pinal.
regards,
Bala
0 down vote favorite
I have created a store procedure for updating the patient name in multiple tables. When I execute it, shows the error like
Server: Msg 170, Level 15, State 1,
Line 1 Line 1: Incorrect syntax near ‘=’.
And my coding is
create procedure uppatname @pid varchar(150),@pname varchar(150)
as begin
declare @i as integer
declare @i1 as integer
declare @ttnm as varchar(100)
declare @tblnam as varchar(100)
drop table tbname
SELECT IDENTITY(int, 1,1) AS RowNumber, table_name
INTO tbname
FROM information_schema.columns
WHERE column_name = ‘pid’
AND table_catalog = ‘hospital’
AND table_name NOT LIKE ‘T%’
SET @i = (select count(*) from information_schema.columns
where column_name=’pid’ and table_catalog=’hospital’
and table_name not like ‘T%’)
SET @i1 = 1
WHILE @i1 <= @i
BEGIN
SET @tblnam = (select table_name from tbname where rownumber = @i1)
SET @ttnm = ('select * from ' + @tblnam + 'where pid = ' + @pid)
EXEC (@ttnm)
SET @i1 = @i1 + 1
END
END
kindly help me
Replace SET @ttnm = (‘select * from ‘ + @tblnam + ‘where pid = ‘ + @pid)
with
SET @ttnm = ‘select * from ‘ + @tblnam + ‘where pid = ‘ + @pid
create table products
(prodid int identity (100, 1) constraint products_pk primary key,
prodname varchar(30) constraint products_prodname_nn not null,
price money constraint products_price_chk check(price >= 0),
qoh int default 0 ,
remarks varchar(100),
catcode varchar(10) references categories (catcode) on delete casecade );
here iam getting error like this incorrect syntax near ‘ )’.
so please help me how to rectify this error
procedure or function expects a parameter which was not supplied
but i checked the procedure ,aspx.cs ,buisness layer and also data acess layer all are correct
one more problem in sql procedure which has one column of date type while executing 1st time all the values are passed
but for 2nd time an error is occured ie error on date type converting nvarchar to date type
how can we convert datatype while inserting values using stored procedure
use cast or convert function
Hi Pinaldave,
i read how we can remove the error that asking for upgrade etc but i am trying to copy one table from one database to other database on same SQL so how can we edit this syntax in the graphicaly
suppose two database are DB1, DB2
Insert into Table1
Select * from DB2..Table2
If table does not exist in db1
Select * into Table2 fromDB2..Table2
declare @ls_sql as varchar(500)
declare @ls_value as varchar(10)
set @ls_value = ”
set @ls_sql = ‘select ‘ + @ls_value
execute(@ls_sql)
why this is isn’t working? tnx for the help..
Are you trying to return empty string?
Use this
declare @ls_sql as varchar(500)
declare @ls_value as varchar(10)
set @ls_value = ”””
set @ls_sql = ‘select ‘ + @ls_value
execute(@ls_sql)
Also refer this to understand how single quotes work in dynamic sql
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
Hi Pinal,
I have checked with the DB compatibility.. its 100 (using SQL Server 2008)
but still getting the same err : Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’.
Can you pls help!! Below is my query..
ALTER PROCEDURE [Audit].[uspGetCardParameterAuditDtl]–3,123,126,122,129,’01-11-2009′,’15-11-2010′,”,0,10,0
@EntityTypeID INT,
@DailyLimitID INT,
@CreditLimitID INT,
@MaxTrnLimitID INT,
@BalanceAmtID INT,
@StartDate NVARCHAR(20),
@EndDate NVARCHAR(20),
@ModifiedByUser NVARCHAR(30),
@PageIndex INT,
@PageSize INT,
@TotalRecord INT OUTPUT
AS
BEGIN
DECLARE @LowerBound INT,@UppperBound INT
DECLARE @Query NVARCHAR(4000),@ModifiedByQuery NVARCHAR(200),@ParamVal NVARCHAR(1000)
SELECT @LowerBound = @PageIndex * @PageSize
SELECT @UppperBound = @PageSize – 1 + @LowerBound
SELECT @StartDate=DATEADD(day,DATEDIFF(day,0,@StartDate),0)
SELECT @EndDate=DATEADD(day,DATEDIFF(day,0,@EndDate),0)+1
IF(LEN(@ModifiedByUser)>0)
BEGIN
SET @ModifiedByQuery=’ AND U.UserName like ”%’+@ModifiedByUser+’%”’
END
ELSE
BEGIN
SET @ModifiedByQuery=”
END
SELECT @Query=’DECLARE @TmpTable TABLE (SNo INT, CardNumber BIGINT,DailyLimit NVARCHAR(100), CreditLimit NVARCHAR(100),
MaxTrnLimit NVARCHAR(100),CCMSBalance NVARCHAR(100),ModifiedBy NVARCHAR(512),ModifyOn DATETIME);
DECLARE @CTEParams TABLE(ParameterName NVARCHAR(100),ParameterId INT,ParameterValue NVARCHAR(100),EntityID INT);
INSERT INTO @CTEParams
Select P.ParameterName , P.ParameterId , PE.ParameterValue, PE.EntityID
from EntityParameterValues PE WITH(NOLOCK)
inner join Parameter P WITH(NOLOCK) ON PE.ParameterId = P.ParameterId
where PE.EntityTypeID=@EntityTypeIDVal;
INSERT INTO @TmpTable
Select ROW_NUMBER() OVER (ORDER BY CardNumber ASC) AS SNo
, C.CardNumber
, DailyLimit = (Select ISNULL(ParameterValue,0) from @CTEParams where ParameterId =@DailyLimitIDVal and EntityId = C.CardId )
, CreditLimit = (Select ISNULL(ParameterValue,0) from @CTEParams where ParameterId =@CreditLimitIDVal and EntityId = C.CardId)
, MaxTrnLimit = (Select ISNULL(ParameterValue,0) from @CTEParams where ParameterId =@MaxTrnLimitIDVal and EntityId = C.CardId)
, CCMSBalance = (Select ISNULL(ParameterValue,0) from @CTEParams where ParameterId =@BalanceAmtIDVal and EntityId = C.CardId)
,U.UserName AS ModifiedBy
,C.ModifiedDate AS ModifyOn
From [Audit].[CardAudit] C WITH(NOLOCK)
INNER JOIN aspnet_Users U WITH(NOLOCK)
ON C.ModifiedBy=U.NumericUserId
WHERE (C.CreatedDate>=@StartDateVal AND C.CreatedDate<@EndDateVAl)'+@ModifiedByQuery+';'+
'SELECT * FROM [@TmpTable] WHERE SNo BETWEEN @LowerBoundVal AND @UppperBoundVal ORDER BY CardNumber;'
SET @ParamVal='@EntityTypeIDVal INT,@StartDateVal NVARCHAR(20),@EndDateVal NVARCHAR(20),@LowerBoundVal INT,@UppperBoundVal INT,
@DailyLimitIDVal INT,@CreditLimitIDVal INT,@MaxTrnLimitIDVal INT,@BalanceAmtIDVal INT, ,@TotalRecordsOut INT OUT'
Print @Query
EXECUTE sp_executeSQL @Query,@ParamVal,@EntityTypeIDVal=@EntityTypeID,@StartDateVal=@StartDate,@EndDateVal=@EndDate,
@DailyLimitIDVal=@DailyLimitID,@CreditLimitIDVal=@CreditLimitID,@MaxTrnLimitIDVal=@MaxTrnLimitID,
@BalanceAmtIDVal=@BalanceAmtID,@LowerBoundVal=@LowerBound,@UppperBoundVal=@UppperBound,
@TotalRecordsOut=@TotalRecord OUTPUT
SELECT @TotalRecord
END
Hi Pinal,
Here is my SQL Script to Create TESTDB. I Try to Create DB on SQL Server 2008 R2 But got Lots error Can you tell me what’s wrong. I am not expert on SQL Server. I can create DB Desing but I am tring to create DB Run-time on my Program. Here are code….
DECLARE @InstanceName varchar(100),
@InstanceLocation varchar(100),
@InstancePath varchar(100)
SELECT @InstanceName = convert(varchar, ServerProperty(‘InstanceName’))
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=’Software\Microsoft\Microsoft SQL Server\Instance Names\SQL’,
@value_name=’SQLEXPRESS’,
@value=@InstanceLocation OUTPUT
SELECT @InstanceLocation = ‘Software\Microsoft\Microsoft SQL Server\’+@InstanceLocation+’\Setup’
EXEC master..xp_regread @rootkey=’HKEY_LOCAL_MACHINE’,
@key=@InstanceLocation,
@value_name=’SQLPath’,
@value=@InstancePath OUTPUT
SELECT @InstancePath
USE [master]
GO
/****** Object: Database [TestDB] Script Date: 12/23/2011 04:56:32 ******/
CREATE DATABASE [TestDB] ON PRIMARY
( NAME = N’TestDB’, FILENAME = N” + @InstancePath + N’\DATA\TestDB.mdf’ , SIZE = 3328KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’TestDB_log’, FILENAME = @InstancePath + N’\DATA\TestDB_log.ldf’ , SIZE = 832KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TestDB] SET COMPATIBILITY_LEVEL = 100
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [TestDB].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [TestDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [TestDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [TestDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [TestDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [TestDB] SET ARITHABORT OFF
GO
ALTER DATABASE [TestDB] SET AUTO_CLOSE ON
GO
ALTER DATABASE [TestDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [TestDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [TestDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [TestDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [TestDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [TestDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [TestDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [TestDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [TestDB] SET DISABLE_BROKER
GO
ALTER DATABASE [TestDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [TestDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [TestDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [TestDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [TestDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [TestDB] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [TestDB] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [TestDB] SET READ_WRITE
GO
ALTER DATABASE [TestDB] SET RECOVERY SIMPLE
GO
ALTER DATABASE [TestDB] SET MULTI_USER
GO
ALTER DATABASE [TestDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [TestDB] SET DB_CHAINING OFF
GO
USE [TestDB]
GO
Error List
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘@InstancePath’.
Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database ‘TestDB’, the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 5011, Level 14, State 5, Line 1
Thanks You Pinal
I have also get this error incorrect syntax near ‘,’. It took me lot of errors and checks to solve this issue.
I am using DSN for SQL Server. While creating DSN, i have selected provider there as SQL Server Native Client 10.0.
After so many checks i have changed this provider to SQL Server Only and then created the DSN then the issue is solved for me.
But thanks for this site also.
CREATE TRIGGER CustomerUpdateMail
– and the table is CustomerInfo
ON CustomerInfo
– trigger is fired when an update is made for the table
FOR UPDATE
AS
— holds the CustomerID so we know which Customer was updated
declare @CustomerID varchar(10)
— holds the body of the email
declare @body varchar(2000)
— holds the old customer name which has been changed
declare @CustomerName varchar(10)
— holds the new customer name
declare @CustomerNewName varchar(10)
— gets the previous customer first name that was deleted
SELECT @CustomerID = Customer_id,
@CustomerName = d.First_Name
FROM deleted d
— gets the new customer first name
SELECT @CustomerNewName = First_Name
FROM inserted
SET @body = ‘Customer with ID=’ @CustomerID ‘ has been updated
with previous First Name is ‘ @CustomerName ‘
and the new First Name is ‘ @CustomerNewName
–xp_sendmail is the extended sproc used to send the mail
EXEC master..xp_sendmail
@recipients = ‘ali_raza_shaikh@datasprings.com’,
@subject = ‘Customer Information Updated’,
@message = @body
GO
i got an error like this
Msg 102, Level 15, State 1, Procedure CustomerUpdateMail, Line 28
Incorrect syntax near ‘@CustomerID’.
Can u tell me where i have to change the values in it
when ever we exeute stored procedure in sql server 2005,
i return value parameter is also attached. I writer procedure
create procedure pname
as
select * from tablename;
but when i execute it
the sql server generte code
use dbname
go
declare @return_value int
exec @return_value=dbo.pname
select ‘Return Value’=@return_value
go
how to avoid this @return_value
SELECT * FROM (select datename(month,docdate)as ‘MONTH’ ,sum(doctotal)AS ‘TOTAL_SALES’,
YEAR(DOCDATE)AS ‘YEAR’,DATENAME(quarter, DOCDATE) AS ‘QTR’
from oinv where convert(varchar(4),year(docdate)) = CONVERT(VARCHAR(4),YEAR(GETDATE()))-4
group by datename(month,docdate),YEAR(DOCDATE),DATENAME(quarter, DOCDATE)) Q1
LEFT OUTER JOIN
(
SELECT TOTAL_SALES,YEAR FROM (select datename(month,docdate)as ‘MONTH’ ,sum(doctotal)AS ‘TOTAL_SALES’,
YEAR(DOCDATE)AS ‘YEAR’,DATENAME(quarter, DOCDATE) AS ‘QTR’
from oinv where convert(varchar(4),year(docdate)) = CONVERT(VARCHAR(4),YEAR(GETDATE()))-3
group by datename(month,docdate),YEAR(DOCDATE),DATENAME(quarter, DOCDATE))) Q2
ON Q1.MONTH = Q2.MONTH
order by q1.qtr
but i get an error like
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near ‘)’.
This part is wrong.
SELECT * FROM (select datename(month,docdate)as
brace is missing. Also why are you using subquery?
Hello,
Here is my C@ code:
private void btn_stergeclient_Click(object sender, EventArgs e)
{
if (txt_idclient.Text!= “” && txt_numeclient.Text!= “” && txt_prenume.Text!= “” && txt_varsta.Text !=”")
{
cn.Open();
comand = cn.CreateCommand();
comand.CommandText = “delete from Clienti where Id_client=’” + txt_idclient.Text + “and Nume=’” + txt_numeclient.Text + ” Prenume= ‘” + txt_prenume.Text + “and Varsta=’” + txt_varsta.Text;
comand.ExecuteNonQuery();
MessageBox.Show(“Client sters”);
cn.Close();
}
}
When I run it I have this error:
Incorrect syntax near “3″
Please help me with it,
Cosmina
that 3 boxes put the value at run time den display result madam i think its a crct solution
when i run the form in access 2010 ,it will automically update the table but at the end of processing it generates error on
sp.update
please help me
harry
Hi,
I have a issue in SQL Server 2012, changing a database from sql server 2005 to 2012.
Already tried to change the compatibility level but I was out of luck.
The “incorrect syntax near @errno” error remains.
Can anyone help me, please?
Thanks
Hi Pinal,
I have a linked server that has a hyphen on its name. When I run a query using the linked server, this returns “Incorrect syntax near ‘-’”..
Could you help me with this? Thanks.
Hi,
I’m getting incorrect syntax near ; in @tableHTML.Can you please help me to solve.
use master
DECLARE @SQL nvarchar(max),@SQL1 nvarchar(max),@SQL3 int,
@tableHTML NVARCHAR(MAX) ,@dbName sysname;
DECLARE DBcursor CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
WHERE name NOT IN (‘master’,'tempdb’,'model’,'msdb’,'AdventureWorksDW’)
AND DATABASEPROPERTYEX(name,’status’)=’ONLINE’ ORDER BY name;
OPEN DBcursor; FETCH DBcursor INTO @dbName;
WHILE (@@FETCH_STATUS = 0) — loop through all db-s
BEGIN
–DECLARE @dbContext nvarchar(256)=@dbName+’.dbo.’+'sp_executeSQL’
SET @SQL = ‘SELECT ”Database: ‘+ @dbName +
‘ table count” = COUNT(*) FROM’+’ ‘+ @dbName+’..sessionsummary where’+’ ‘+ @dbName+’..sessionsummary.usn>2′ ;
SET @SQL1 = ‘SELECT ”Database: ‘+ @dbName +
‘ table count” = COUNT(*) FROM’+’ ‘+ @dbName+’..sessionsummary where’+’ ‘+ @dbName+’..sessionsummary.usn=0′ ;
–SET @SQL3= convert(int,@sql)
PRINT @SQL
print @SQL1;
– SELECT ‘Database: AdventureWorks table count’ = COUNT(*) FROM sys.tables
–SELECT CONVERT(int, ‘@sql’, 0) AS [Style 0, character to int];
–select @sql= convert(@sql as int)
EXEC sp_executeSQL @SQL;
EXEC sp_executeSQL @SQL1;
–
–set @sql1= cast(@sql1 as int) ;
if ((@sql)>0 AND (@SQL1)=0)
begin
declare @Message nvarchar(100)
–declare @time varchar(20);
–declare @date datetime;
–set @date=CONVERT(VARCHAR(19), CAST(s.lastsyncdate AS DATETIME), 107)
–set @time=@date
declare @fullbody nvarchar(1000)
set @Message = ‘[All markets] – ‘+CAST(@SQL as varchar)+’ Users not performedthe DB reset’
SET @tableHTML =
‘N”’ +
‘N”h2, body {font-family: Geneva, calibri;} table{font-size:12px; border-seperate:seperate;} td{background-color:#B22222; padding:3px;} th{background-color:#800080 ;}’ +
‘N”Not performed the DB reset Users’ +
‘N”’ +
‘N”’ +
‘N” ‘ +
‘N” ‘ +
‘N”’ +
‘N”RepnameRepID’ +
‘N”Max USN countLast DB Reset countLast sync date DATELast DB Reset DATE
‘ +
CAST ( ( ‘SELECT
td = R.Repname, ”,
td = R.RepID, ”,
td = S2.USN, ”,
td = S1.USN, ”,
td = ‘+CONVERT(VARCHAR(25), CAST(s2.lastsyncdate AS DATETIME), 107)+’,”,
td = ‘+CONVERT(VARCHAR(25), CAST(s1.lastsyncdate AS DATETIME), 107)+’
from’+”+ @dbname+’..sessionsummary s1
inner join’+”+ @dbname+’..sessionsummary s2 on ‘+”+@dbname+’..S2.Repid=’+”+@dbname+’..S1.Repid
inner join’+”+ @dbname+’..Rep_table R on ‘+@dbname+’..R.repid=’+@dbname+’..S2.Repid
AND ‘+@dbname+’..S2.lastsyncdate=(select max (‘+@dbname+’..s2.lastsyncdate) from ”’+@dbname+’..sessionsummary s2
where’+ @dbname+’..s2.usn>3 and ‘+@dbname+’..s2.repid=s1.repid)AND ”’+@dbname+’..s1.lastsyncdate=(select max(”’+@dbname+’..s1.lastsyncdate) from ‘+@dbname+’..sessionsummary s1
where ‘+@dbname+’..s1.usn=0 and ‘+@dbname+’..s1.repid=’+@dbname+’..s2.repid)’+
‘FOR XML PATH (”tr”), TYPE
) AS NVARCHAR(MAX))’ +
‘N’+” ;–>getting error here
print @tableHTML
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ”Pinal profile”,
@recipients=”xxxx@gmail.com”,
@importance = ”HIGH”,
@subject = ”Action Required :: users need to perform DB reset”,
@body = ”@tableHTML”,
@body_format = ”HTML” ;
end
FETCH DBcursor INTO @dbName;
END; — while
CLOSE DBcursor; DEALLOCATE DBcursor;
Incorrect syntax near ‘unsigned’.?????
hi
i have a following code:
Alter PROCEDURE p_details
(@P_id int
,@P_name NVARCHAR(50)
,@P_age nvarchar(50)
,@P_gender nvarchar(50)
,@StatementType nvarchar(50) =”)
AS
BEGIN
IF @StatementType = ‘Insert’
BEGIN
insert into Table_1 (P_id,P_name,P_age,P_gender) values( @P_id,@P_name,@P_age,@P_gender)
END
IF @StatementType = ‘Select’
BEGIN
select * from Table_1
END
IF @StatementType = ‘Update’
BEGIN
UPDATE Table_1 SET
P_name = @P_name,P_age = @P_age,P_gender = @P_gender
WHERE P_id = @P_id
END
ELSE IF @StatementType = ‘Delete’
BEGIN
DELETE FROM Table_1 WHERE P_id = @P_id
END
END
GO
I shows error on execution
Msg 208, Level 16, State 6, Procedure p_details, Line 25
Invalid object name ‘p_details’.
please tell me what’s going wrong
and my stored procedure is also nt show in the list of stored procedures
check database that using if correct then Replace ALTER by CREATE
Hi Pinal,
Would you please help me fixing this error.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘=’.
SELECT tblSubCategory1.Sub_Cat1, tblSubCategory1.Description, tblSubCategory1.Picture
FROM tblSubCategory1, tblItemMaster, tblItemQty
WHERE (tblSubCategory1.Sub_Cat1 = tblItemMaster.Sub_Cat1 AND tblItemMaster.ItemCode = tblItemQty.ItemCode)
ORDER By (tblItemQty.DepCode = ‘NEWJ’)
AND (tblSubCategory1.Code = ‘FLT’)
AND (tblCategory.Type = ‘Sell’ tblCategory.Type = ‘Both’)
Hi Pinal,
Would you please help me fixing this error.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ‘=’.
SELECT tblSubCategory1.Sub_Cat1, tblSubCategory1.Description,
tblSubCategory1.Picture
FROM tblSubCategory1, tblItemMaster, tblItemQty
WHERE (tblSubCategory1.Sub_Cat1 = tblItemMaster.Sub_Cat1
AND tblItemMaster.ItemCode = tblItemQty.ItemCode)
ORDER By (tblItemQty.DepCode = ‘NEWJ’)
AND (tblSubCategory1.Code = ‘FLT’)
AND (tblCategory.Type = ‘Sell’ tblCategory.Type = ‘Both’)
Hi Pinal,
We have a SQL Server Database on 2008 version which was restored from 2005 Version.
We are using OLEDB from .net Code to connection to SQL Sever.
Problem is, during load testing of our application we are getting errors like “Incorrect Syntax near” while it is working fine in normal scenario.