Executing Stored Procedures

Jun 9, 2004

Hello,

I know that syscomments stores the stored procedures in chunks of 4000 characters. Some of my Stored proc's are >4000 characters.I use a temporary table to stock them after some modifications made to them.How can I execute them one by one, do i need to concatenate one whole stored proc's text in one single line(if yes, how can that be done?) Can variables be dynamically created, assigned values and then destroyed? Please help.

View 3 Replies


ADVERTISEMENT

Executing Stored Procedures From Asp.net

Nov 2, 2006

I have the following sp..REATE Procedure SSSP_VehicleReg_Add    @DECAL_NO varchar  (9)   , @NAME varchar  (26)  , @SSN varchar  (9)   , @DLN varchar  (10)   , @Address_1 varchar    , @Address_2 varchar    , @City varchar  (50)  , @State_Code varchar    , @Zip_Code varchar  (9)  , @Country_Code varchar  (4)   , @PHONE varchar  (12)   , @VM varchar  (10)   , @MODEL varchar  (10)   , @VLPNUMBER varchar  (10)   , @VLPSTATE varchar  (2)  , @Date_Iss datetime, @Date_Exp datetime, @tran_Date datetime, @Op_Code varchar (20) , @ReturnStatus   integer output, @ReturnMessage  Varchar (50) output /* Adds a record to the Vehicle Registration table.*/ AS     If @Decal_no = ' '          Begin  Set @ReturnStatus = 0         Set @ReturnMessage = 'Record not added Key Fields are not valid'  End     Else          If dbo.VehicleReg_Check (@Decal_No) = 0       Begin    Insert INTO VehicleReg (Decal_NO, Name, SSN, DLN, Address_1, Address_2, City, State_Code,                            Zip_Code, Country_Code, Phone, VM, Model, VLPNUMBER, VLPSTATE,                             Date_Iss, Date_Exp, Tran_Date, Op_Code)                  Values   (@Decal_NO, @Name, @SSN, @DLN, @Address_1, @Address_2, @City, @State_Code,              @Zip_Code, @Country_Code, @Phone, @VM, @Model, @VLPNUMBER, @VLPSTATE,               @Date_Iss, @Date_Exp, @Tran_Date, @Op_Code)    Set @ReturnStatus = 1         Set @ReturnMessage = 'Record added'          End     Else        Begin  Set @ReturnStatus = 0         Set @ReturnMessage = 'Record not added' EndGO That is being called via...Dim SqlCmd As New SqlCommand("SSSP_VehicleReg_ADD", MYGF.cnMydb)Dim Current_Time As String = CStr(Now.Date + Now.TimeOfDay)SqlCmd.CommandType = CommandType.StoredProcedureSqlCmd.Parameters.Add("@Decal_No", SqlDbType.VarChar, 9, txtDecal.Text)SqlCmd.Parameters.Add("@NAME", SqlDbType.VarChar, 26, txtName.Text)SqlCmd.Parameters.Add("@SSN", SqlDbType.VarChar, 9, txtStuid.Text)SqlCmd.Parameters.Add("@DLN", SqlDbType.VarChar, 10, txtDln.Text)SqlCmd.Parameters.Add("@Address_1", SqlDbType.VarChar, 50, txtAddress_1.Text)SqlCmd.Parameters.Add("@Address_2", SqlDbType.VarChar, 50, txtAddress_2.Text)SqlCmd.Parameters.Add("@City", SqlDbType.VarChar, 50, txtCity.Text)SqlCmd.Parameters.Add("@State_Code", SqlDbType.VarChar, 2, ddlState.Text)SqlCmd.Parameters.Add("@Zip_Code", SqlDbType.VarChar, 9, txtZip.Text)SqlCmd.Parameters.Add("@Country_Code", SqlDbType.VarChar, 4, " ")SqlCmd.Parameters.Add("@PHONE", SqlDbType.VarChar, 12, txtPhone.Text)SqlCmd.Parameters.Add("@VM", SqlDbType.VarChar, 10, txtMake.Text)SqlCmd.Parameters.Add("@MODEL", SqlDbType.VarChar, 10, txtModel.Text)SqlCmd.Parameters.Add("@VLPNUMBER", SqlDbType.VarChar, 10, txtTagNo.Text)SqlCmd.Parameters.Add("@VLPSTATE", SqlDbType.VarChar, 2, " ")SqlCmd.Parameters.Add("@Date_Iss", SqlDbType.DateTime, 8, txtIssDate.Text)SqlCmd.Parameters.Add("@Date_Exp", SqlDbType.DateTime, 8, txtExpDate.Text)SqlCmd.Parameters.Add("@tran_Date", SqlDbType.DateTime, 8, Current_Time)SqlCmd.Parameters.Add("@Op_Code", SqlDbType.VarChar, 20, "xxxx")Dim ReturnStatus As New SqlParameter(("@ReturnStatus"), SqlDbType.Int, 1, ParameterDirection.Output)Dim ReturnMessage As New SqlParameter(("@ReturnMessage"), SqlDbType.VarChar, 50, ParameterDirection.Output)SqlCmd.Parameters.Add(ReturnStatus)SqlCmd.Parameters.Add(ReturnMessage)SqlCmd.ExecuteNonQuery()Which fails with the following error...System.Data.SqlClient.SqlException was unhandled by user code  Class=16  ErrorCode=-2146232060  LineNumber=0  Message="Procedure 'SSSP_VehicleReg_Add' expects parameter '@DECAL_NO', which was not supplied."  Number=201  Procedure="SSSP_VehicleReg_Add"  Server=  Source=".Net SqlClient Data Provider"  State=6  StackTrace:       at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()       at Vechicle_Registration.Move_to_database() in C:InetpubwwwrootssccintranetVechicle_Registration.aspx.vb:line 304       at Vechicle_Registration.BtnSave_Click(Object sender, EventArgs e) in C:InetpubwwwrootssccintranetVechicle_Registration.aspx.vb:line 34       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Any help would be appreciated      

View 2 Replies View Related

Executing Stored Procedures From Asp.net

Nov 2, 2006

I changed "@Decal_No" to "@Decal_NO" same error 

View 2 Replies View Related

Executing Stored Procedures

Jan 29, 2007

Hi All,I am running SQL2000. Can anyone tell me how I can use the contents ofa table as parameters for a stored procedure that resides on adifferent SQL server?I have a table:Customers:Cust_IDCust_NameCust_ContactCust_PhoneI need to execute a stored procedure and pass each of the above asparameters:@CustID, @CustName, @CustContact,@CustPhoneThe stored procedure also returns an error code.Thanks,Danny

View 3 Replies View Related

Executing Stored Procedures

Jul 3, 2007

I have a table with a list of stored procedures, I iterate using a Foreach loop which i iterate through an ADO.NET recordset that I created by executing a select SQL statement.



Now my problem is that in the foreach loop i have an execute sql task container which basically just executes these stored procedures using the 'exec ?' statement (i grab the name of each procedure into a variable). However, error trapping seems to be very difficult. I just figured out how to get a ReturnCode from the execute statement, but apparently I have to use it outside the Execute SQL task ....prolly use an IF condition to see if its not 0....but how do I go about doing this?



Also, I want to error trap if a stored procedure doesnt execute because of some error, how could i possibly handle an error like that? I dont want the foreach iteration to stop on an error because a stored procedure wasnt called either, but currently it does stop the entire package.



Thoughts anyone?

View 4 Replies View Related

Executing SQL Stored Procedures In VB

Aug 15, 2007

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

View 1 Replies View Related

Executing More Than One Stored Procedures In A DataReader

Nov 28, 2007

Hey guys,
I have found out that we can execute multiple queries and receive multiple resultsets in a SqlDataReader by executing the queries with ";" separators,
However, what if we wanted to execute two sqlcommand storedprocedures? are there any other way rather than placing "Execute sp1;Execute sp2" in the command text?
I would like to do it in a way whereby I can pass in two storedprocedures with parameters binding capability rather than execute sp1(param1, param2);execute sp2(param1, param2, param3)
Hope to get some suggestions and advice from you guys,
 Thank you very much in advance.

View 5 Replies View Related

Executing Distributed Stored Procedures

Oct 21, 2007

Hi
I have a database created on SQL Server 2000 server. And I have created a new database with SQL Server2005. I want to extract some data from old SQL Server2000 database. I have written a SP at SQL Server 2000 database and I want to know how to execute a SQL Server2000 SP from SQL Server2005.

Thanks & Regards!

View 2 Replies View Related

Issue While Executing Stored Procedures

Jan 8, 2008



I would be grateful if someone can provide some kind of explanation over why this issue has happened.

I had the following statements in a query window.

exec dbo.usp_sProc1
go
exec dbo.usp_sProc2

go
exec dbo.usp_sProc3
go


In order to monitor the progress of each procedure, I have a table called progress_log into which each step executing in the procedure is written. Each procedure has a "Begin" and "End" text written to this progress_log.

When I clicked the execution button on the window, I made the assumption that each stored procedure will finish before the next one executes. However something different happened.


The progress_log shows the following

S/N proc_Name Step
1 sProc1 Begin
2 sProc1 sProctep1
3 sProc1 sproc1: Step2

4 sproc1 sproc1tep3
5 sProc2 sproc2:Begin
6 sProc2 sProc2tep1
7 sproc2 sProc2tep2
8 sproc2 sproc2:End
9 sProc3 Begin
10 sProc3 Sproc3: Step1


From the above (S/N: 5) , it can be seen that the procedure did not finish executing Sproc1 because there was no "END" statement written to the Progress_Log before the "Begin" statement of SProc2.

On checking the error_log, I found there were no errors in the stored procedure. However, a message was written to the query window about insufficient space on the database at S/N:10 in the log above.

How is it possible that the Sproc2 stored procedure can be started without the Sproc1 stored procedure having ended?

I can give more explanation if not clear.

many thanks for any help


View 5 Replies View Related

... Executing Stored Procedures In An INSERT Statement ...

Aug 28, 2002

I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.

The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.

I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.

Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?

TABLE
-----
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);

MS SQL SERVER STORED PROCEDURE:
-------------------------------
CREATE PROCEDURE nextval
@sequence varchar(100)AS
BEGIN
-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @sequence_id int
set @sequence_id = -1

UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence

RETURN @sequence_id
END

View 1 Replies View Related

Transact SQL :: Executing Stored Procedures And Threading

Jul 10, 2015

If I have several stored procedures, all of them making inserts/updates to the same tables, what is the safest way to run them in sequence?

The context is an asp.net application; I have an aspx page where a click on a button (or more) will launch stored procedures in execution. I have encountered the unfortunate situation before, when stored proc #2 started long before stored proc #1 finished and this caused problems. 

If I group all stroed procs in one that simply calls all of them in the needed sequence:

exec stproc1
exec stproc2
.....
exec stprocn

Will they execute in a single thread? Is there any guarantee that stproc2 will be executed when stproc1 finishes, and so on?

View 2 Replies View Related

How To Get The Results When Executing Extended Stored Procedures.

May 28, 2007

Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.



|_N_T_|

View 3 Replies View Related

Transact SQL :: Executing Stored Procedures Asynchronously / In Parallel

May 30, 2015

I have about 30 different reports that I want to pull into a dashboard. I need to make sure that they don't execute in serial to get good performance.

There are two ways I can approach it

1) I can create a stored procedure for each report and then make sync calls for each of the reports from the web site. So, basically this will be controlled from the web end.

2) I can do this from the SQL Server database, if there is someway to execute these stored procedures in parallel.

View 8 Replies View Related

List Of Stored Procedures With Permission For Executing For User

Jul 26, 2007

I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?

Thanks for tips

View 4 Replies View Related

Executing SQL Server 2005 Stored Procedures On Windows 2003

Jul 18, 2007

I'm a new developer to both SQL Server 2005 & Windows 2003, so forgive me if this question seems a little too basic. I'm coming from a Oracle and UNIX background.



I've create a stored procedure in SQL Server 2005. I now want to execute this from the command line in Windows 2003. Eventually, I want our UNIX scheduler, autosys (which runs on a different UNIX machine obviously) to be able to execute this. In my old environment, I created a UNIX shell script as a wrapper let's say 123.sh. This shell script would accept as a parameter the name of the stored procedure I wanted to execute. If this stored procedure also had parameters it needed to be passed to it, I would have strung these values out in the command line in UNIX. Two examples of how the command line in UNIX I used to execute the Oracle stored procedure might look are listed below.



123.sh sp_my_stored_procedure input_parm1 input_parm2



123.sh sp_different_stored_procedure input_parm1



This way anytime I created a new stored procedure, I could reuse the shell script wrapper 123.sh and just pass in the name of the newly created stored procedure and any parameters it needed.



How can I accomplish this same type of functionality in the SQL Server 2005/Windows 2003 environment.



Thanks, Jim

View 4 Replies View Related

SQL 2012 :: Parameter Error When Executing A Package With Built In Stored Procedures

Jul 23, 2014

I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.

@Cycle sql_variant = 2
WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication
AS
BEGIN
SET NOCOUNT ON;
declare @execution_id bigint

[code]....

When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.

View 4 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Qu

Jan 24, 2008



Hi,

I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window

Please see the image through this url http://kyxao.net/127/ExecutionProblem.png


Any ideas for this issue?

Thanks a lot

View 1 Replies View Related

Stored Procedure Executing Durations Are Different Between Executing From Application(web) And SQl Server Management Studio - Query Window

Jan 23, 2008

Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack 

View 2 Replies View Related

Executing N Procedures In 1 Round Trip

Apr 30, 2006

w/ SqlServer, is there anyway to pack a number of calls to the same stored procedure into a single round-trip to the DB short of dynamically writing a T-SQL block?  For example, if I'm calling a procedure "Update Contact" which takes 2 params @Campaign, @Contact 20 times how would I pass in the values for those 20 diffrent versions?

View 6 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Executing (from Sql Server 2000) Procedures From Oracle Package Through Linkserver

Feb 16, 2004

Hello,

I deaply need to know how to execute procedures from package in oracle, from sqlserver 2000 using linkserver.

Thank you very much,
Victor
DBA

View 2 Replies View Related

SQL Server 2012 :: Permissions For Executing Procedure Which Executes Other Procedures?

May 13, 2015

I have a stored procedure which executes about forty other stored procedures in several different databases. All of these other procedures truncate tables and insert new data into those tables selected from still other tables.

I want to run this top-level procedure using an account which can't do anything else.

Is there a simple way to give it all the permissions it needs without empowering it to do anything else?

View 0 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related

Executing DTS From Stored Procedure

Oct 11, 2001

Hi,

I have create a DTS package "test"

i see the name is stored in msdb.sysdtspackages

how do i run this "test" package from stored procedure

thx
vik

View 1 Replies View Related

Executing A Stored Procedure

Nov 1, 2006

Hello,

I'm using Sql Server 2005, and I am receiving an error when I attempt to run a stored procedure and I have no clue why. Can someone please help?

I receive an error when I attempt to execute the following stored procedure:
exec INSERT_OBJ
1234,
'Name',
123,
NULL,
GetDate(),
'system'

The error I receive is: "Incorrect syntax near ')'" All of the values are valid values and valid types.

Is it possible to be an error within the stored procedure itself? This error makes it sound like the syntax of my attempt is incorrect, thus it never gets ran.

Thank you ,
Crystal

View 2 Replies View Related

Need Help In Executing A Stored Procedure Using Op

Jun 12, 2008

Hi All,

I am trying to execute a "ServerB" Stored Procedure in "ServerA".This SP is in Multiple DB's in ServerB.
I am trying to use Openquery and Dynamic SQL to do that.But I am having issues.
Intially i am trying to pass just one DBname as parameter..if it returns values then i can use cursor or other options to retrieve for multiple DB's
Please Help!!!

Ex:



DECLARE @TSQL varchar(8000), @DBNAME char(20)
SELECT @DBNAME = 'DB1'

SELECT @TSQL = 'SELECT * FROM OPENQUERY(serverB'+','+''exec '' + @DBNAME + ''.dbo.sp_StoredProcedure''+')'
EXEC (@TSQL)


Thanks in Advance!!!

View 3 Replies View Related

Executing Stored Procedure Using VB6.0

Jul 10, 2007

Hi,
Can anyone tell me how to execute a stored procedure using vb6.0.I am able to connect to sqlserver from my application.Simple select queries are working.

The stored procedure contains a select statement at the end.I want to get that as the resultset in vb6.0.How do I do this.

Keerthi

View 1 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Executing Stored Procedure... Hangs...

Oct 2, 2007

Hi
I have a stored procedure in SQL Server 2005. It make a backup of a database and restores it to a different name.I use ASP.NET and Framework 1.1.It works really fine when I use SQL Server 2000.
But!When trying to do the same thing on SQL Server 2005, the database seems to be created "half way" I can see that the database is created, but after the name is the text Restoring....It never finish restoring.... and nothing shows in the server logs.
Any ideas?Differences between SQL Server 2000 and SQL Server 2005 that I must be aware of?Priviliges?ConnectionString parameters?Drivers?
I'm using .NET Framework 1.1ODBC (SQL Native Client)
Here is the Store procedure code:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 ALTER PROCEDURE [dbo].[CreateProjectDataBase]
@AFModelDatabaseProject varchar(200),
@TemplateDbBackupFileAndName varchar(200),
@DatabaseName varchar(200),
@DataFilePathAndName varchar(200),
@LogFilePathAndName varchar(200)
AS
 
BACKUP DATABASE @AFModelDatabaseProject TO DISK = @TemplateDbBackupFileAndName
WITH INIT
RESTORE DATABASE @DatabaseName FROM DISK = @TemplateDbBackupFileAndName
WITH MOVE 'AdressTNG_Project_Data' TO @DataFilePathAndName,
MOVE 'AdressTNG_Project_Log' TO @LogFilePathAndName
 
and here is how it is called from within .NET:this.odbcCreateDataBaseCommand.CommandType = System.Data.CommandType.StoredProcedure;
this.odbcCreateDataBaseCommand.Parameters["@AFModelDatabaseProject"].Value = afModelDataBaseName;this.odbcCreateDataBaseCommand.Parameters["@TemplateDbBackupFileAndName"].Value = TemplateDbBackupFileAndName;
this.odbcCreateDataBaseCommand.Parameters["@DatabaseName"].Value = dbName;this.odbcCreateDataBaseCommand.Parameters["@DataFilePathAndName"].Value = DataFilePathAndName;
this.odbcCreateDataBaseCommand.Parameters["@LogFilePathAndName"].Value = LogFilePathAndName;this.odbcCreateDataBaseCommand.CommandText = "{ CALL CreateProjectDataBase(?,?,?,?,?) }";
this.odbcCreateDataBaseCommand.ExecuteNonQuery();
RegardsTomas

View 2 Replies View Related

Problem Executing A Stored Proc

Mar 25, 2005

Hi All,
 I have stored proc that processes about 60,000 rows using a cursor. When I call the SP from Query Analyzer, I get the following error message after processing about 12,000 records :
 
Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (InvalidParam()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
ODBC: Msg 0, Level 16, State 1
Communication link failure
 
Connection Broken
 
 
12614 records
 
What can i do to make this SP run sucessfully ? I even tried using a table variable instead of a cursor, but got the same result.
 
THE output from SP_CONFIG is
Option                                                                                                config_value
----------------------------------------------------------------------------------------------------------------------------------







affinity mask
0

allow updates
0

awe enabled
0

c2 audit mode
0

cost threshold for parallelism
5

Cross DB Ownership Chaining
0

cursor threshold
-1

default full-text language
1033

default language
0

fill factor (%)
0

index create memory (KB)
0

lightweight pooling
0

locks
0

max degree of parallelism
0

max server memory (MB)
2147483647

max text repl size (B)
65536

max worker threads
255

media retention
0

min memory per query (KB)
1024

min server memory (MB)
0

nested triggers
1

network packet size (B)
4096

open objects
0

priority boost
0

query governor cost limit
0

query wait (s)
-1

recovery interval (min)
0

remote access
1

remote login timeout (s)
20

remote proc trans
0

remote query timeout (s)
0

scan for startup procs
0

set working set size
0

show advanced options
1

two digit year cutoff
2049

user connections
0

user options
0
 
 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved