Sql Server Stored Procedures, Just One Thing After Another

Jul 2, 2007

 In a previous post, someone helped me with creating stored procedures, and I am grateful because I am transitioning from the Access World.

Anyway, I get an error at .ExecuteNonQuery in visual studio 2005 when I run the following code: 

Dim strsql As String
        Dim strconn As String
        strsql = "sp_Roster"
        strconn = "server=xxxx; user=xxxx; pwd=xxxx; database=xxxx;"
        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 = 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

The error was:  Error converting data type char to int.

The stored procedure in sql server was as follows 

CREATE PROCEDURE sp_Roster
    -- Add the parameters for the stored procedure here
@TeacherID varchar(50),

@ClassID  int,

@sID int,

@sLastName varchar(50),

@sFirstName varchar(50),

@sMiddleName varchar(50),

@Student varchar(50),

@Password varchar(50)

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    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)
END
GO

The error also says "sqlexception was unhandled by user code.

This is strange because this code worked perfectly when connecting to Access and when I used oledb. 

So how is it the code's problem?  Is the stored procedure causing the error or the code.  Can someone please help. thanks.
 

<Edited by Dinakar Nethi>

Please mask your useird/pwd info in the connection string when posting to a public forum like this

</Edit>

View 2 Replies


ADVERTISEMENT

Are Clr Sp's And Extended Stored Procedures The Same Thing In SS2005?

Feb 22, 2008

...we understand that perhaps some xp's might come with the product, but for sp's that we author ourselves, are they two different things in 2005, and if so, why would one be chosen over the other?

View 1 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

Localization In SQL Server- Is There Such A Thing?

Apr 30, 2007

Hello all,
I'm working on an ASP.NET with a SQL server for database. Some of the tables, for example, contain information such as different types of Fabrics (silk, cotton, etc..) . I'd like to have this table localizable (English and French for instance). Is this possible ? Is there an equivalent of resource files in SQL server ?
Or do I have to do this manually ? (have 2 separate fields in the table for those 2 locales)

View 1 Replies View Related

Array (or Similar Thing ) In Sql Server

Jul 4, 2005

I would like to write a fun or stored procedure to do some operation. It require me to know that what category is currently belong to certain people(people_table: category_table1               to           Many)However, when i use the select statement in stored proc, it return a set of result, not a scalar , therefore, i cannot use the variable to hold it. In addition, there are no array in SQL server.Question:1. Is there any way to hold the collection of result(like array)?2. Also, how to determine to use fun or stored procedure?(Since a integer is need to return by them)Thx

View 4 Replies View Related

SQL Server Sluggish First Thing In The Morning

Sep 15, 2005

Every morning our sql server runs very slowly which means our log on
page times out on a simple query. If we stop and start the sql server
everything runs fine for the rest of the day until the follwing
morning. the server is not used out of business hours excpet for a few
very small and simple jobs to delete records, These all run to
completion. Any help with this would be much aprreciated! Thanks.
Rob

View 2 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

SQL Server Stored Procedures/VB.Net

Mar 24, 2004

IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example.....

View 3 Replies View Related

Stored Procedures In Sql Server 6.5

Jan 4, 2000

Hi,

Everytime after I restore any database on the sql server 6.5, I have to recompile all the stored procedures in that database.Is there any method to overcome this problem?


Thanks,
Manu.

View 1 Replies View Related

ADO And SQL Server Stored Procedures

Apr 19, 1999

Trying to run a SQL Server 6.5 stored procedure via ADO 2.0 in Visual Basic 6. The stored procedure moves roughly 10000 records from one table to another within the same database. The procedure works just fine when run from a SQL editor such as I/SQL, but only transfers a few hundred records when it is executed from a VB application using ADO 2.0.
The guilty code can be seen below. Any thoughts?

Public cnSQL As New ADODB.Connection
Public qry As New ADODB.Command

With cnSQL
.ConnectionString = ConnectStringSQL
.ConnectionTimeout = 20
.Open
End With

Set qry.ActiveConnection = cnSQL
qry.CommandType = adCmdStoredProc
qry.CommandTimeout = 120

qry.CommandText = "sp_VGInsertBOMStructure"
qry.Execute

Peter

View 1 Replies View Related

ADO SQL/Server Stored Procedures

Jun 6, 2002

I have a stored procedure on SQL/Server 2000 that inserts into a #temptable. When I execute it from Query Analyzer it works fine.
When I execute it from an ASP using ADO it gives me an Open Error message. When I comment out the
#temptable the ASP works fine. Can someone shed some light on this? Do I have to do an ADO opent on the
#temptable?

View 4 Replies View Related

SQL SERVER Stored Procedures

Apr 11, 2006

Is there any application which can tell you the procedure called within a procedure.

View 2 Replies View Related

Stored Procedures In SQL Server

May 16, 2008

I come from a MySQL background, and have recently began migrating to SQL Server.

I can't, for the life of me, find an example of a stored procedure that does the following in SQL Server:
- Multi-lined
- Contains both input and output parameters
- Sets one of the output parameters within the stored procedure

Can anyone provide some insight? I.e. Why does the following not work?

IF NOT EXISTS (SELECT * FROM syscomments WHERE id = object_id('usp_test'))
BEGIN
CREATE PROCEDURE "usp_test"
(@num1 INT, @num2 INT, @result INT OUTPUT)
AS
BEGIN
DECLARE newValue INT;
SET @newValue = num1 + num2;
SET @result = @newValue;
END;
END;

I get these errors (I'm using the Management Studio Express)

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'PROCEDURE'.
Msg 155, Level 15, State 2, Line 7
'INT' is not a recognized CURSOR option.
Msg 137, Level 15, State 1, Line 8
Must declare the scalar variable "@newValue".
Msg 137, Level 15, State 2, Line 9
Must declare the scalar variable "@newValue".

View 8 Replies View Related

Creating Stored Procedures In Sql Server

Jul 2, 2007

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?  

View 2 Replies View Related

Sql Server Stored Procedures Grouping

Feb 25, 2008

In Oracle database we can group our stored procedures in packets. It is very useful when we are creating big business solutions with hundreds stored procedures. Can we do something like this in SQL server? 

View 2 Replies View Related

Debugging SQL SERVER Stored Procedures

Jun 8, 2005

I don't see the option to debug a stored procedure. I right click on
the procedure and should'nt it appear in the properties window. Do I
need to install some extra component to get the debugger.

View 4 Replies View Related

How Do You Transfer Stored Procedures To Another SQL Server?

Jan 28, 2006

How do you transfer stored procedures from SQL Express 2005 to MS SQL 200 ?  I am using the Personal home page starter kit and also need to know if I have to attach and detach the database to my ISP's server?thanks,

View 2 Replies View Related

Stored Procedures In MSSQL Server

May 6, 2006

hi, anyone can guide me on how to create stored procedures in MS SQL Server?

View 1 Replies View Related

SQL Server 7.0 System Stored Procedures

May 1, 2001

About a year ago we inherited a SQL server (7.0) from another division of our company. The time has come to migrate the SQL Server functionality onto a new machine. Before I do this though, I need to figure out what the initial installation configuration was so I can set up the new server with the same specs. I am looking for a system stored procedure(s) that can tell me the following:

Case sensitivity, accent sensitivity, SQL build (SP), character set, etc. Basically I need to know what checkboxes were ticked during the initial set up of the server.

Any help would be greatly appreciated. TIA.

View 2 Replies View Related

Versioning SQL Server Stored Procedures With VSS?

Oct 19, 1999

Hi everyone,

Is versioning SQL Server 7.0 stored procedures with VSS possible? If so,
how? How are people versioning stored procedures out there?

Thanks in advance...
bth@prucka.com

View 1 Replies View Related

Exporting Stored Procedures In Sql Server

Nov 8, 2004

Hi,

Does anyone know how to export all stored procedures attached to a database (MS SQL Server) into a sql script. Please help.

Thanks

View 2 Replies View Related

Wildcards In SQL Server Stored Procedures

Jul 20, 2005

I thought this problem would go away over the Christmas holiday, butof course it did not. I'm trying to write a stored procedureincorporating wildcards, so I can search for variations. Example, ifname 'Smith' is submitted, sproc should retrieve all recordscontaining 'John Smith', 'Zenia Smith', 'Smithfield & Co.' You get theidea.Using SQL Query Analyzer, the queryselect * from filewhere name like '%smith%'works like a charm.But if I write a stored procedure declaring the variable @name andusing a where clause 'where name like '%@name%'', I get zero results.The query doesn't bomb. It just doesn't produce anything - even thoughI know there are records that meet the criteria.Any ideas? Or are sprocs and wildcards incompatible?

View 1 Replies View Related

Calling Sql Server Stored Procedures From C#

Apr 3, 2008



I have a simple stored procedure:


create procedure sp_testres

@mult1 int,

@mult2 int,

@result int output

as

select @result = (10*@mult1) + @mult2

go



When I call it


declare @result int

exec sp_testres 5, 6, @result output

print @result

(Result is correctly shown as 56).

I then in C# wrote the following:



m_cmd.CommandText = "sp_testres";

SqlParameter param2 = new SqlParameter("@mult2", SqlDbType.Int);

param2.Value = 6;

SqlParameter param1 = new SqlParameter("@mult1", SqlDbType.Int);

param1.Value = 5;

SqlParameter param3 = new SqlParameter("@result", SqlDbType.Int);

param3.Direction = ParameterDirection.Output;

m_cmd.CommandType = CommandType.StoredProcedure;

m_cmd.Parameters.Add(param1);

m_cmd.Parameters.Add(param2);

m_cmd.Parameters.Add(param3);

m_cmd.ExecuteNonQuery();


This works and param3.Value holds the result value.
I also notice that I can supply the parameters in any order, and things work fine.

What I want to know is: can I call the stored procedure with parameters, where I haven't supplied the parameter name, and just rely on the parameter order matching instead?

View 5 Replies View Related

Force SQL Server To Recompile Stored Procedures Every Time They Run (SQL Server 7/2000)

Apr 27, 2005

This is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled storedprocedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the natureof a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highlyspecialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by thesavings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of yourapplication to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this:    CREATE PROCEDURE ProcName        @Param int /* ... other parameters */        WITH RECOMPILE    AS /* ... procedure code follows */
If we omit "WITH RECOMPILE", what will be the consequence? Thanks
 

View 3 Replies View Related

SQL Server 2005 Stored Procedures Running On Server 2003 Machines

Jan 20, 2006

Stupid question but please be gentle and answer anyway please....

Background: We have SQL Server 2003 (32bit) running on our servers. Our .Net applications (from old release of VS) are still running on them and using the old databases. From what I understand there is no immediate plans to upgrade the servers. However the developers were just given this new upgrade (2005) SQL Server and VS (and fixing depreciated code etc in the .net apps).

Question: Can the applications and new stored procedures written via the 2005 environment be deployed successfully on the 2003 Servers? Same goes with Reporting Services?

View 1 Replies View Related

SQL Server 2005: Copying Tables And Stored Procedures Between Databases On Same Server

Mar 5, 2008

This question is about SQL Server 2005:
I have been trying to figure out how to copy tables and stored procedures between 2 databases (on the same server) using SQL Server Management Studio. I have tried right clicking on the table name, "script table as", "drop to", "clipboard", then I click on the 2nd database, and then click on the "tables" . I change the name of the database and click "execute". This creates the table but does not copy the data. I have also tried "create to" "clipboard" and "insert to" "clipboard" and cannot seem to be able to figure out how to get the results that I want. I am new at this but need to get the tables with the data copied along with the stored procedures, even if I have to do them one at a time. When I was using SQL Server 2000, I was able to use DTS to copy objects to other databases easily. Can someone please tell me a way to accomplish what I need to do? I have gotten information here before that was very useful and was hoping that someone can help me again.Thank you so much. Carol Quinn

View 9 Replies View Related

Unable To Find Stored Procedures On Server

Aug 8, 2007

I have just successfully published an web app and db to a server
but when the web app tries to use a stored procedure it says that
it cannot find stored procedure.....in my data base......eeeer
anyone had this problem

View 6 Replies View Related

Creating SQL Server Stored Procedures In Deployment

Sep 20, 2007

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.

View 1 Replies View Related

SQL Server Stored Procedures, Tables And Parameters

May 19, 2004

Hi,

I was just wondering if something could be explained to me.

I have the following:

1. A table which has fields with data types and lengths / sizes
2. A stored procedure for said table which also declares variables with datatype and lengths/ sizes
3. A function in written in VB .net that uses said stored procudure. The code used to add the parameters to the sql command also requires that i give a data type and size.

How come i need to specify data type and length in three different places? Am i doin it wrong?

Any information is greatly appreciated.

Thanks

Im using SQL Server 2000 with Visual Studio .Net using Visual Basic..

View 1 Replies View Related

SQL Server How To's &&&& Dyamic Joins In Stored Procedures (I Think)

Jan 1, 2005

First, does anyone know any good SQL Server sites with articles and whatnot on query design, etc. Not so much basic "How to get data out of your table", but more complex topics like conditional stored procedures, working with triggers, etc. The MSDN is helpful but I often have trouble understanding what's going on.





And second, any pointers (or links) on how I can go about having a stored procedure query a pair of product tables to get information to display in my shopping cart? I've got:


Cart Items StdProducts CustomProducts
----------- ------------- -----------------
partID stdpartID custompartID
IsCustom (Bit)
description description


So depending on if the 'IsCustom' field is True or False, I want to join the [cartitems].[partID] to either the [StdProducts].[stdpartID] or the [CustomProducts].[custompartID] to get the description and other information.

Eventually, I'll probably need to branch this type of procedure out even further (not just either/or scenario) to include the option of pointing to 6-12 different child tables depending on criteria within the parent table for a different scenario. Depending on performance, I could either hard code in the various child tables or have another table containing the table names and the appropriate key that would indicate which table to use.

Hopefully that made some sense, since I'm not entirely positive how to go about this type of thing or what you would call whatever I'm trying to do (and thus what I would be searching for).

View 1 Replies View Related

Scheduling Stored Procedures For MS SQL Server 2000

Mar 31, 2005

Is there any way to make MS SQL Server 2000 to perform some stored procedures, say 3 times a day? If so, how? Thanks.

View 2 Replies View Related

Transfer Only The Stored Procedures To A Remote Server.

Jan 23, 2006

Hi,
I uploaded a database to a remote server (ISP) who has SQL2005 as do I. They got the database okay, but not all the stored procedures went along for the ride
Can anyone tell me how to just simply transfer the stored procedures only to the remote server?
Failing that, can you tell me how to export the entire database and be sure that it all goes, not just the tables. I assume you would have to do this via a script because the export wizard only lets you copy the files and I have dome this but the stored procedures do go along.
Any help?
Tia

View 3 Replies View Related

Oracle-SQL Server Conversion And Stored Procedures

Feb 15, 2000

Hello Everyone,
First problem :
I have an application which uses Power Builder and Oracle and my job is to convert it so that it can run on a SQL server database.
Oracle allows empty strings - '' - to mean NULL. If the application inserts an empty string in a column of a table, Oracle takes it to mean NULL.
How can i have this functionality in SQL Server?

Second Problem:
SQL Server does not allow stored procedures like sp_addlogin and sp_droplogin etc., to be executed from within a transaction. So everytime i need to execute these stored procedures thru my application, i disconnect and set autocommit to true and connect again. after executing these stored procedures, i disconnect again , set autocommit to false and then connect again. is this how it should be done or is there some better way of doing it.

All help will be highly appreciated. I really need help on these problems - fast.
Thanks

View 2 Replies View Related







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