.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
MarieAdela
Imran Ghani
Post New Web Links

Incorrect syntax near '%'. When trying to create/alter system stored procedure

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :Sql Server
 

Sql Server 2005 SP3

After finally figuring out that for whatever reason the sp [sys].[sp_refreshsqlmodule] was not loaded I found the procedure on another machine and tried to create it on the machine missing the sp. I get an error: Incorrect syntax near '%'. The error applies to 2 lines in the sp:

 

EXEC %%Object(MultiName = @name).LockMatchID


View Complete Post


More Related Resource Links

Create stored procedure from asp.net

  

Hi,

we are creating a custom report tool, which could be used for generate the report as per end user's needs. In that we are providing an option as user could create a query and procedure as well.

In sql server we can use "EXEC" function for execute dynamic query.

Could anyone help me for create the dynamic query in Oracle?

I just tried with "execute immediate", which would throws error as 

"insufficient privileges".

Please help me.

 

 


Execute system stored procedure

  
This should be very simple, but I haven't found the solution yet.  I'm writing a Q&D application to setup log shipping for a large number of databases.  I need to execute several stored procedures (e.g. sp_add_log_shipping_primary_database) which will return a value plue two output parameters that I need.  I've taken the code generated by SQL Server and executed it in a SMSS query window.  I've tried configuring an ADODB command with EXEC sp_name parm1, parm2, ..., parm 12 OUTPUT and setup parameters on the command without success (The connection cannot be used to perform this operation.).  I tried stringing all of the statements needed together in one line separated by semi-colons (executed without error but didn't return any values). I'm using VB2010 and SQL Server 2008.  Any suggestions would be appreciated.

Need to create a stored procedure on a db if that db exists..

  
IF   EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'test') BEGIN use test Create   Procedure test @empno int, @empname varchar (50), @loc varchar (50) as print   'it is a msg' end   getting the error msg Msg 156, Level 15, State 1, Line 6 Incorrect syntax near the keyword 'Procedure'. I need to deploy this sp  only if that db exists to around 400 db servers in a batch... I heard within begin.. end .. we should not include DDL's .. but i need to find a work around...  thanks   

want to create common Stored procedure

  
Hello, I have more than 20 tables, for this i want to create a common Stored procedure which fetches data by id Column So I tried like this,but it doesnt work ALTER PROCEDURE [dbo].[ERS_SP_GetAllTableDataByID] -- Add the parameters for the stored procedure here @TableName nvarchar(100), @ColumnName nvarchar(100), @ColumnVal nvarchar(100) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @ExecQuery nvarchar(100) SET @ExecQuery =@TableName -- Insert statements for procedure here select @ExecQuery = 'SELECT * FROM [' + @TableName + '] WHERE ' + '[' +@ColumnName +']=' + @ColumnVal exec (@ExecQuery) END please help me out Regards,

How to create a second independent transaction inside a CLR stored procedure?

  
I use the context connection for the "normal" work in the CLR procedure.But I need to commit some data in every case. So I need to create a second transaction which is independent from the calling transaction.What is the best way to do this inside a CLR procedure?Thanx a lot

Error when trying to create a stored procedure

  
Hello allI am using c# to create a new stored procedure and i keep getting a stupid failed operation exception. This is the code i use static private void CreateProcedure(string connectionString, string userName, string password, string DTName, List<DataColumnsObj> parametersIn, List<DataColumnsObj> parametersOut) { SqlConnection connection = new SqlConnection(connectionString); ServerConnection serConnection = new ServerConnection(connection.DataSource); serConnection.LoginSecure = false; serConnection.Login = userName; serConnection.Password = password; Server server = new Server(serConnection); string storedProcedureName = _procedureName; StoredProcedure sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); sp.Refresh(); try { sp.Drop(); sp = new StoredProcedure(server.Databases[connection.Database], storedProcedureName); } catch (Exception ex) { } sp.TextMode = false; sp.AnsiNullsStatus = false; sp.QuotedIdentifierStatus = false; AddParametersStoredProcedure(sp, parametersIn, false); if (parametersOut != null) { AddParamet

stored procedure syntax question

  
I have this stored procedure to calculate aging on invoices: CREATE PROCEDURE spInvoiceAging -- Add the parameters for the stored procedure here @PeriodEndDate Datetime, @Balance money AS SELECT InvoiceMaster.InvNo, InvoiceMaster.CustId, InvoiceMaster.DueDate, CASE WHEN datediff(day,InvoiceMaster.DueDate, @PeriodEndDate) BETWEEN '0' AND '30' then Sum(@Balance) as InvCurrent, CASE WHEN datediff(day,InvoiceMaster.DueDate, @PeriodEndDate) BETWEEN '31' AND '60' then Sum(@Balance) as 31TO60, CASE WHEN datediff(day,InvoiceMaster.DueDate, @PeriodEndDate) BETWEEN '61' AND '90' then Sum(@Balance) as 60TO90, CASE WHEN datediff(day,InvoiceMaster.DueDate, @PeriodEndDate) > '90' then Sum(@Balance) as 90PLUS FROM InvoiceMaster GROUP BY InvoiceMaster.InvNo, InvoiceMaster.CustId, InvoiceMaster.DueDate GO I am getting this error: Msg 156, Level 15, State 1, Procedure spInvoiceAging, Line 8 Incorrect syntax near the keyword 'as'. I am not sure what I am doing wrong. TIA Ryan

Incorrect output from Stored Procedure

  

Hello! I am trying to get a stored procedure to show me an email address in the EmailAddress column of the table based off of the input, which in this case is the CustomerID. So basically, say 3 is the CustomerID I want the sproc to show the email address for customer id 3, but instead it's just showing me the EmailAddress column heading. Here is my code below, help!

Thanks

DROP PROCEDURE Email
GO
CREATE PROCEDURE Email(@cid INT, @email [VARCHAR] = OUT)
AS
SELECT * FROM SalesLT.Customer
WHERE @email = EmailAddress AND @cid = CustomerID
PRINT @email


can I create a job to call a stored procedure?

  

Hello,

 

Can I create a job to call a stored procedure everyday? If yes, how to do it?

 

Thanks


how to create stored procedure in sql server ???

  

here i have 3 attributes i want to write a sp it should take 1input value by tht i get remaining 2out put values

 table name: Employee

attributes: id,name,sal;

help me in this....


if this exected i should get succecss msg also and boolen is true...

else


flase and not exectuted msg...


also


Create a stored procedure that will take different files like .csv,.txt and create a table in my dat

  

Hi All,

I am new in SQL Server.Currently I am using SQL Server 2008.

I have to create a stored procedure that will take different files like .csv,.txt and create a table in my database.

At a single time i am using only one file like .csv,.txt.

Here I am generating a XML file which contains script to create table and how to map data into table.

Here we are using XML as a middle man which gives us idea about file structure and how to map data into table.

Can any one guide me how can i do this thing?

Thanks & Regards

Gaurav Jain


Create a stored procedure that will take these different files like .csv,.txt and create a table in

  

Hi All,

I am new in SQL Server.Currently I am using SQL Server 2008.

I have a lot of different files like .csv,.txt files.

I have to create a stored procedure that will take these different files and create a table in my database.

At a single time i am using only one type of file like it may be .csv or may be .txt.

I am creating a XML file which contains script of Create Table and how to map table in sql server?

Here XML is working as middle man.

Can any one guide me how can i do this things?

Thanks & Regards,

Gaurav Jain


how to create stored procedure in sql server 2005 and how to use that stored procedure in asp.net

  

Hi friends ,

       I am very newbie

I have created stored procedure and student database and also asp.net application for asp.net page

but it could not found stored procedure what is the mistake actually I don't no

Please help me



Warm Regards

Durga


How to create a batch file using a stored procedure

  

Hi,

Please let me know how to create a batch file with some content in it, using a stored procedure in SQL 2000.

Thanks in advance.

Regards,

-Naren.


System Stored Procedure permission

  

Hi,

I am not able to see the permission for the System stored procedure( like sp_add_job) and extended system procedure like xp_execresultset in SQL 2005.  I could see the permission in SQL 2000(right click->properties-> permission tab). 

In SQL 2005, I don't see the Properties when I right click except Modif,custom reports and refresh?

Where to check the permission for the system stored procedures?

Pls let me know.

 

Thanks.

 


Stored procedure syntax errors

  

Hello,

Right now we have a stored procedure in another server ("A") that we are using in a visual studio job to populate a table in our sql server. Instead of having that stored procedure in server "A," I am moving it to our current sql server but I am having some syntax errors and I can't seem to figure them out. I know that the servers use different syntax but for the most part it's mostly the same. So now I am trying to use just Transact-SQL to build this stored procedure in our sql2005 database - any help would be greatly appreciated! 

 I am using the command "openquery"  to select the fields that I need from the linked server. Here is the stored procedure:

SET ANSI_NULLS ON

GO

 

Need to execute ALTER DATABASE inside stored procedure

  

SQL 2005 Standard.

I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

I tried to modify create proc with execute as owner but nothing changed.

How can I do ?

Thanks.


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend