I am creating a database application that is accessed through a .NET front end.
What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyser I get multiple errors but the one that is coursing me the most bother is
€˜Server: Msg 156, Level 15, State 1, Procedure procedureName, Line 134
Incorrect syntax near the keyword 'procedure'.€™
What does this mean and why can€™t I run more than once Stored Procedure at once?
I am creating a database application that is accessed through a .NET front end. What I want to do is run a SQL script that will create my DB, create my indexes and enforce my constraints (all of which I have done) but I also want to create my stored procedures in the same script also.
When I merge all my stored procedures (about 16) into one file and run it in SQL Query Analyzer I get multiple errors but the one that is coursing me the most bother is
‘Server: Msg 156, Level 15, State 1, Procedure procedureName, Line 134 Incorrect syntax near the keyword 'procedure'.’
What does this mean and why can’t I run more than once Stored Procedure at once?
I want to create a replication on PUBS database between the servers "DENVER" and "VANCOUVER". I created a publication on "DENVER" using SALES table of PUBS database. In the properties of publication, I selected all the options of "Subscription creation and synchronization" under "Subscription options".
Then I created a pull subscription on "VANCOUVER" using PUBS database. As data of SALES table is same in both servers and also I want to use subscribers schema and data, I chose the option "No, the subscriber already has the schema and data" for initializing subscription. Now the problem is when I do updations in sales table, subscription is returning error "Could not find stored procedure 'sp_MSupd_sales'". I was found that subscription is not creating any of the stored procedure that is needed for subscribing from publication. Please help me to solve this problem.
Hi,All,I want to create a stored procedure,it should accept two input parameters one is General variable and other one is Table name. How to pass table name as a input parameter. I have created following one,but giving error.
CREATE PROC SP_VOTERSLIPS @BOOTHID VARCHAR(14),@TABLENAME VARCHAR(255) AS BEGIN SELECT V.BOOTHID,B.BOOTHNAME,B.BOOTHADDR1,B.BOOTHADDR2,B.ENGBOOTHADDR1,B.ENGBOOTHADDR2,C.CONSSTUTIONNAME,V.SNO,V.HOUSENO,V.VOTERNAME, V.GUARDIANTITLE,V.GUARDIANNAME,V.GENDER,V.AGE,V.VOTERIDNUMBER FROM @TABLENAME V LEFT JOIN TBLBOOTH B ON (B.BOOTHID=V.BOOTHID) LEFT JOIN TBLCONSTTUTION C ON (C.CONSSTUTIONID=V.CONSSTUTIONID) WHERE B.BOOTHID=@BOOTHID END
I just installed sql server 2005 on my machine. I've been using Access for some time and have created my stored procedures easily in it. However, this sql server 2005 is so complicated. In Access, all I had to do was "Create a New Query", write my sql statement, name and save it. However, I am having so much trouble with this sql server. For example, when I create a stored procedure in sql server, it has a .sql extension. I am assuming the stored procedure is a file. However, after saving it, I expand my database, expand programmability, and expand stored procedures, yet my new stored procedure is not there. It's placed in a file called projects. I need to access this stored procedure from code, but in visual studio, I get an error message, "Cannot find stored procedure sp_Roster". Here is the code I used to access my stored procedure from vb.net. I thought it would work but it didntDim comm As New SqlCommandDim strsql As StringDim strconn As String strsql = "sp_Roster" strconn = "server=Home; user=sa; pwd=juwar74; database=Book;" With comm .Connection = New SqlConnection(strconn) .CommandText = strsql .CommandType = CommandType.StoredProcedure With .Parameters.Add("TeacherID", SqlDbType.Char) .Value = "DawsMark@aol.com" End With With .Parameters.Add("ClassID", SqlDbType.Int) .Value = CInt(classid) End With With .Parameters.Add("sID", SqlDbType.Int) .Value = ssID End With With .Parameters.Add("sLastName", SqlDbType.Char) .Value = lastname End With With .Parameters.Add("sFirstName", SqlDbType.Char) .Value = firstname End With With .Parameters.Add("sMiddleName", SqlDbType.Char) .Value = middlename End With With .Parameters.Add("Student", SqlDbType.Char) .Value = fullname End With With .Parameters.Add("Password", SqlDbType.Char) .Value = password End With .Connection.Open() .ExecuteNonQuery() With comm.Connection If .State = ConnectionState.Open Then .Close() End If End With End With Here is my procedure that I created and that was saved as sp_Roster.sql in sql server CREATE PROCEDURE sp_Roster ASBEGIN SET NOCOUNT ON; -- Insert statements for procedure here INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@TeacherID, @ClassID, @sID, @sLastName, @sFirstName, @sMiddleName, @Student, @Password)ENDGO Why isn't ado.net finding it in sql server. Is the sqlstr correct?
Hey guys,I'm having a problem. I've been given a task to complete. I was given a database, and asked to wrap a website around it with certain functionality. I did this, and added seven stored procedures in the proccess. Everything works, Business Logic Layer, Data Access Layer, error validation, even screwed around with SQL-injection protection. Lovely, yes?However, when my little website gets tested, it's going to be plugged into a fresh database - the exact same database I was given, only with different data in the tables. My stored procedures won't be in that database. I can detect if my stored procedure doesn't exist easily enough by catching the error at runtime and checking the code. I would like to create the stored procedure inside that catch block. I just don't know how.The easy answer is just to use embedded SQL in my application instead of stored procedures. This isn't a commercial application, it's just a task I've been given to test my abilities. But embedded SQL is icky. I'd rather do it properly.
Is it possible to create an extended stored procedure in C Sharp. This is for Sql Server 2000.
Books online mentions that you have to use c / c++ to create an extended stored procedure. However have Microsoft added any support so that the same thing can be done through a simpler language like C Sharp.
I would like to know if for example i have some tables in my DataBase and i need from each table to get for instance, the Name by the ID, how can i make only one procedure which use parameters and variables so i can use this SP to get the Name By ID for all the Tables?
Our DB has around 30 SProcs - I need to move them into a script, so that it can be easily added to another server. The way I'm creating the script is to highlight all the SProcs, then copy - in my notepad screen, I paste, which gives me one script, which includes all the individual creation scripts for the Sprocs. However, I'm getting an error when I create the script - Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'SP_MySproc'. The stored procedure will still be created. So - what's a good, and/or easy way to structure the script, so that I can easily find WHERE to put Which SProc Script, in the list?
I got the following error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I got the following T-SQL error messages: Msg 111, Level 15, State 1, Procedure sp_getRecords, Line 8 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 10 Incorrect syntax near the keyword 'PROC'. Msg 156, Level 15, State 1, Procedure sp_getRecords, Line 11 Incorrect syntax near the keyword 'PROC'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 14 Incorrect syntax near 'EXE'. Msg 102, Level 15, State 1, Procedure sp_getRecords, Line 20 Incorrect syntax near 'updateRecord'. ///////////////////////////////////////////////////////////////////////////////////////////////////// Please help, tell me what is wrong in my execution of the code and advise me how I can correct this problem. Thanks, Scott Chang
I am running MS SQL Server 7 with SP3 installed and am having some problems getting replication to work correctly. I want to replicate all tables from one database to another database on the same server. I can setup replication, add publications and add subscriptions without any problem and all goes well until the replication process starts when the job aborts because it cannot find the sp_MSins..., sp_MSupd... or the sp_MSdel... stored procedures. I have used the New Publication wizard to create a Transactional publication, published all tables and allowed the wizard to use the default stored procedures which it says will be created when the subscribers are initialized but although I have created push subcriptions the stored procedures are never created and hence the replication fails whenever the job runs.
I have SA access to the databases concerned and as far as I am aware the jobs are running as my login - can anybody help me out as this is beginning to drive me nuts.
I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message:
I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql. I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem.
I have a simple question: Can I have two or more stored procedures that begin transactions but the last stored procedure will commit all transactions if there was no error? In other words, I have a stored procedure that performs some legacy stuff. I do not want to rewrite that logice, execpt I am putting it into a Stored Procedure since it currently is embedded SQL. To support newer functionality, I am writing data to additional new tables. However, I do not want the *new* things to occur if there is an error. This is easy enough, I dont call the Stored Procedure for the new functionality if there was an error. However, if there was not an error and the newer stored procedure is called AND there is an error in the new stored procedure, I want to ROLLBACK the changes from the proceeding stored procedures. To my understanding, I can name transactions but that is only to indicate in the logs what transactions have failed. I thought about not using transactions for any of the individual stored procedures and calling them from a main stored procedure. The main stored procedure will have a BEGIN TRY and an END TRY (I am using SQL Server 2005) and at the top (right after the try) I will have a BEGIN TRANSACTION. In the BEGIN CATCH I will have a ROLLBACK and at the end I will have a COMMIT. If any of the stored procedures fail at any point it will cause the catch to occur thus rolling back all of my transactions. This would be the easiest way but I still need to deal with the question of, "What happens if any single stored procedure is called?" I guess I could have try and catches in each individual stored procedure for that. I just want to make sure I am not overlooking something simple. I think I have the right idea by doing it this way I mentioned above. Because this is critical billing processing I just want to have some reassurance that this would work. Thank
My stored procedure looks like this: ALTER PROCEDURE dbo.Calculate AS BEGIN SET NOCOUNT ON declare @lastUpdate varchar(15) declare @sql nvarchar(4000) declare LastUpdate cursor for select updateID from currentUpdate OPEN lastUpdate FETCH NEXT FROM LastUpdate INTO @lastUpdate SELECT @sql = 'INSERT INTO A ...WHERE ..' + @lastUpdate EXEC(@sql) SELECT @sql = 'INSERT INTO B ...WHERE...' + @lastUpdate EXEC(@sql) SELECT @sql = 'UPDATE ...WHERE...' + @lastUpdate EXEC(@sql) CLOSE LastUpdate DEALLOCATE LastUpdate END
When I run this stored procedure from my web page, only the first sql is executed and then I get a timeout message. When I execute the stored procedure manually on the server, all the sqls are executed correctly. How can I solve this problem? Thank You.
Am i the only one who is forced to open multiple instances of SQL Server Enterprise Manager in order to edit more than one stored procedure at a time? A lot of times i'll be comparing two sprocs or writing one based off of a "template" i wrote earlier. But i have to open two instances and alt-tab between them. Is there an easier way? (aside from pasting it into a notepad and using that)
Hi, I am hoping someone can help me with creation of a stored procedure. What i would basically like to do is have a SELECT stored Procedure that gets values from one table and then uses these values to retrieve data from another table. i.e. I would like to retrieve 2 dates from another table (the table has a single record that holds the 2 dates) then I would like to use those two dates to retrieve all records of another table that fall between those 2 dates. As I know nothing about stored procedures I'm not even sure how to go about this?? Regards. Peter.
I am using multiple stored procedures which are using same set of tables at a time . As stored procedures dont have any DMLs. they are just select statement copied into a temporary table for further processing. My issue is ,I dont want to wait one stored procedure until the other stored procedure is completed.
as one stored procedure is taking 43 secs and another sp is taking one min .they are conmbinely taking 1:43 mins where i want to take just 1 min which is the time took by second sp
I want this because i am calling all the stored procedures more than 5 in my reporting services to show in one report which is taking huge time
Please suggest me how to proceed here.i am stuck
what should i do with the tables or stored procedures?
I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.
So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.
what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable
can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.
Hello All,I can not figure out how to accomplish the task that I want. So, please help.I have two tables, Parent and Child tables. For both Tables I have stored procedures for inserting records. I simply pass parameters for field values to stored proecdures and Stored procedures simply insert record with those values. I am first inserting record in Parent table and then in child table via calling their stored procedures one by one.Now, what I want to do is, after inserting record in parent table successfully, If record can not be inserted in child table successfully then I want to rollback, I also want to undo the insertion operation done in previous parent table's stored procedure call. The problem is, I can not span a Transaction across multiple stored procedure. If I begin a Transaction in a stored procedure for a Parent table, then I have to either roll back or commit that transaction in the same stored procedure. How can I span a transaction across multiple stored procedures, so that I can rollback in the Child Table's stored procedure in such a way that the Record Insertion of the Parent table can be cancelled. What to do...? Please help, it's urgent. Thanks to all.
I have my database and i generated the script that creates the entire database. My database has some stored procedures.
When i create the script without the stored procedures, everything works fine when i try to reinstall the database. But if i create the script with the stored procedures, i get the following error message when i try to reinstall the database:
Code:
System.Data.SqlClient.SqlException: 'CREATE/ALTER PROCEDURE' must be the first statement in a query batch. Incorrect syntax near the keyword 'PROCEDURE'. Must declare the scalar variable "@time1".
as i understand, i must have 1 storedprocedure per script. I tried separating one stored procedure per script and everything works fine, the problem here is that i have many many stored procedures. Now, is there a way to have multiple stored procedures in one single sqlscript?
Do i really need to separate my stored procedures in one script per stored procedure or is there any way to put them all in one single script?
I have a Report which has 8 stored procedures to get 8 resultant data sets . the stored procedures are almost similar such that they have only difference is the where clause and column getting returned.
So ,every stored access same set of tables and temporary tables getting created to store some set of active data derived from big table.
what happening is ,when i run the stored procedures individually in query analyser ,it is taking the time which is accepatable individually,but when i keep them in the same report. it is taking the time which is equal to sum of all the times taken by the stored procedures ran individually in query analyser which is some what not acceptable
can anybody through an idea,what can be done here. i already thought of locks and kept set transaction isolation level read uncomitted for all the stored procedures.but the time taking is same.
