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
ADVERTISEMENT
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
Nov 2, 2006
I changed "@Decal_No" to "@Decal_NO" same error
View 2 Replies
View Related
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
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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