I just created a stored procedure in SQL Server 2005 management studio. I went to my db and expanded the programmability folder then right clicked the stored procedures folder and created a stored procedure. I saved the procedure as sp_getDistance in the default folder SQL managment studio picked. Now when I went back to that stored procedure folder I did not see my stored procedure sp_getDistance, all that was there was the system stored procedure folder. Did I save the stored procedure in the wrong place, or what did I do wrong. I can't find the procedure anywhere, its just sitting in my My DocumentsSQL Server Management StudioProjectssp_getDistance.sql folder. Thanks,Kyle Spitzer
I tried to use the "How to call a Parameterterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsoft.com/kb/308049 to learn "Use DataReader to Return Rows and Parameter" in my VB 2005 Express. I did the following things:
1) created a stored procedure "pubsTestProc1.sql" in my SQL Server Management Studio Express (SSMSE):
USE pubs
GO
Create Procedure TestProcedure
(
@au_idIN varchar (11),
@numTitlesOUT Integer OUTPUT
)
As
select A.au_fname, A.au_lname, T.title
from authors as A join titleauthor as TA on
A.au_id=TA.au_id
join titles as T
on T.title_id=TA.title_id
where A.au_id=@au_idIN
set @numTitlesOUT = @@Rowcount
return (5)
2) created a project "pubsTestProc1.vb" in my VB 2005 Express and copied the following code from http://support.microsoft.com/kb/308049 (i.e. Added the code to the Form_Load eventQL_Client) :
Imports System.Data
Imports System.Data.Client
Imports System.Data.SqlType
Imports System.Data.Odbc
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection & _
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class ////////////////////////////////////////////////////////////////////////////////////////////////////////////////// I compiled the above code and I got the following 15 errors: Warning 1 Namespace or type specified in the Imports 'System.Data.Client' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 2 9 pubsTestProc1 Warning 2 Namespace or type specified in the Imports 'System.Data.SqlType' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 3 9 pubsTestProc1 Error 3 Type 'SqlConnection' is not defined. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 10 25 pubsTestProc1 Error 4 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 15 30 pubsTestProc1 Error 5 Name 'testCMD' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 17 9 pubsTestProc1 Error 6 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 20 23 pubsTestProc1 Error 7 Name 'RetValue' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 21 9 pubsTestProc1 Error 8 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 23 23 pubsTestProc1 Error 9 Name 'auIDIN' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 24 9 pubsTestProc1 Error 10 ')' expected. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 26 28 pubsTestProc1 Error 11 Name 'NumTitles' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 27 9 pubsTestProc1 Error 12 Name 'auIDIN' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 29 9 pubsTestProc1 Error 13 Type 'SqlDataReader' is not defined. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 32 25 pubsTestProc1 Error 14 Name 'RetValue' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 39 47 pubsTestProc1 Error 15 Name 'NumTitles' is not declared. C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005ProjectspubsTestProc1pubsTestProc1Form1.vb 40 52 pubsTestProc1 //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// First, I am completely lost here alreay. Second, I should have the following code from http://support.microsoft.com/kb/308049 too:
OLE DB Data Provider
Dim PubsConn As OleDbConnection = New OleDbConnection & _
("Provider=sqloledb;Data Source=server;" & _
"integrated security=sspi;initial Catalog=pubs;")
Dim testCMD As OleDbCommand = New OleDbCommand & _
("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As OleDbParameter = testCMD.Parameters.Add & _
Console.WriteLine("Number of Records: " & (NumTitles.Value)) ////////////////////////////////////////////////////////////////////////////////////////////////////// Now, I am completely out of touch with these two sets of the code from the Microsoft KB 308049 and do not know how to proceed to get the following output stated in the Microsoft KB 308049-see the below:
4. Modify the connection string for the Connection object to point to the server that is running SQL Server.
5.
Run the code. Notice that the ExecuteScalar method of the Command object returns the parameters. ExecuteScalar also returns the value of column 1, row 1 of the returned rowset. Thus, the value of intCount is the result of the count function from the stored procedure. //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// Please help and tell me what I should do to get this project landed on the right track.
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Hi All:First of all, I wanna say that I am very new to Stored Procedure, so pardon me for asking questions that seemed like an ABC questions.Alright, so I am creating this login page for my users. They provide their username and pwd. Upon clicking the Login button, some codes were supposed to see if the username exists in the stored procedure. If it is, a welcome message with their username is posted else, an error message will be posted.I've created my stored procedure: (so far, this is what I know)create procedure sp_testing @username varchar(20),@pwd varchar(16) as select userName from testingwhere userName = @userName and password = @pwdYup, I am stuck! Haha. I tried to write some codes on my code-behind, but there's an error or it doesn't work.I hope you all know what I am trying to say. So basically, the inputs are only the username and pwd. But I've been cracking up with these stuffs for three days and still have no luck. Could anyone out there help me?Thanks a lot!
I'm looking to write my first stored procedure. I need to find out the Identity for a record that I just did an INSERT on. I've achieved the proper outcome with a second query, but I'd really like to use a SP here. I think I could use some instance of the @@identity method in SQLServer 7. Can anyone offer some suggestions? Thanks
I am trying to write a stored procedure to do the following below:
The printed check file has a name that looks like this: A111111B222222222C3333333333D50E50.PDF.* In the example name above, 111111 is the check number, 2222222222 is the routing number and 3333333333 is the account number.
The letters define the piece of data that follows.* * ·******** A = Check number ·******** B = Routing number ·******** C = Account number
Steps: a. stored procedure to pull the check number, routing number and account number from tblCheckRegister. b. Join them together in proper format. b. Once it has done that, do a wildcard search on a table called tblSIValidate column SIBarCode. c. The return value should be the entire SIBarCode column.* Append the .PDF to it and look in the same image directory as the cancelled check.
I am used to VB.net and vbscripting and the syntax there. I am sure there are things below in the stored procedure that are not correct. Any help is appreciated.
I have a stored procedure I created in SQL server 2005. Now I need to call the stored procedure in C#. Can someone help me out here? What is the C# code I need to call this stored procedure? I have never done this before and need some help. CREATE PROCEDURE [dbo].[MarketCreate] ( @MarketCode nvarchar(20), @MarketName nvarchar(100), @LastUpdateDate nvarchar(2),) ASINSERT INTO Market( MarketCode MarketName LastUpdateDate)VALUES( @MarketCode @MarketName @LastUpdateDate )
I have created a stored procedure only with an insert statement in sql server 2005. How can i call this stored procedure through code in ASP.NET page using vb. i want to pass one parameter that comes from a text box in asp.net page. my emailid is: g12garg@yahoo.co.in pls reply. Thank you Gaurav
Does anyone give me syntax for adding a bcp script within a stored procedure..I had done it once 3 yrs back does'nt seem to work now, and I do not know where I am going wrong?? Thanks
Even though this may be not right place with this issue I would like to try! I facing with the problem “Object Variable or With Block variable not set” while I am trying to execute the stored procedure from Ms. Access form. I need some help very badly or maybe a good sample of code that works in this issue is very welcome.
i created a stored procedure and below is the code segment of it;
create Procedure test1 @price as varchar(50) output, @table as varchar(50) = '' AS
Declare @SQL_INS VarChar(1000)
SELECT @SQL_INS = 'select ['+@price+'] from ['+@table+']'
Exec (@SQL_INS)
Procedure gets 2 parameters, one of them is just INPUT parameter and the other one is both OUTPUT and INPUT. What i wanna do is to get the result set of this procedure to use in my application.
i am working on VC++ (visual studio2005 with sql200).i have created one stored procedure to insert data into table. i want to call this strd procedure in the VC++ main.cpp file....
Could you please help me for the correct syntax code with example.
Hi there. My problem is: I have two stored procedures. 1. SELECT A FROM B 2. SELECT C FROM D WHERE A = EXEC First procedure
The meaning: First procedure gets some Id from B table. The second one gets a DataSet by this Id number. The problem is that when I getting an Id from first proc I use SELECT, than in the second one I use EXEC, and in the end, seconf procedure returns two DataSets. The first contains an Id from first procedure, second contains a valid DataSet. Therefore my application falls because it suppose that valid data in first DataSet. Hoow can I call to stored procedure from another stored procedure without creating two DataSets? P.S. I already tried to use return instead of select in the first procedure. Same result. Thank you
I have a query that select rows from the employees,salary_head and salary_group tables this is the query SELECT TOP 100 PERCENT dbo.salary_head.salary_group_id, dbo.salary_group.salary_group, dbo.salary_head.amount, dbo.grade_level.[level], dbo.employees.employ_name, dbo.employees.work_id, dbo.employees.company_id, dbo.employees.designation, dbo.salary_head.level_id, dbo.employees.terminate, dbo.employees.banks_id, dbo.employees.bank_account_no FROM dbo.employees INNER JOIN dbo.salary_head INNER JOIN dbo.salary_group ON dbo.salary_head.salary_group_id = dbo.salary_group.salary_group_id ON dbo.employees.level_id = dbo.salary_head.level_id INNER JOIN dbo.grade_level ON dbo.employees.level_id = dbo.grade_level.level_id ORDER BY dbo.grade_level.level_no DESC, dbo.salary_head.salary_group_id i also have a table called payrollers1 with the following fields payroll_id int auto period_id int employee_id level_id designation_id banks_id bankaccount_no salarygroup_id AmountI am trying to write a stored procedure that will run the above query and then insert the values of the employee_id,level_id,designation_id,salary_group_i d,amount rows into the payroller table. As for the period_id i want the Stored procedure to look up the max payperiod_id value in the payperiod table. I am totally new to stored procedure and do not know how to write this code. Can somebody help me with this code.
Hi everybody. I am not sure if I'm even posting in the right forum... I tried my best.
I need to write a stored procedure that'll work with two values, @FromValue and @ToValue.
In Query Analyzer, when this stored procedure is executed with user-given values (@FromValue/@ToValue), I need it to loop until the program reaches the ToValue number.
Lets say I executed the SP with given values @FromValue = 1 and @ToValue = 5, the result should be
1, 2, 3, 4, 5
Listing out all numbers 1 to 5, with a comma and space after each number.
Hi, I would like to trigger a DTS or a stored procedure from asp.net 1.1 BUT I don't want to wait for it to finish. In fact the DTS/Storeproc calculates values into different tables. Those values are not needed immediately. The calculation takes between 20 or 30 minutes. Do you have any idea how to do it ? Thanks
I have gridview display a list of users. I have added a column for a check box. If the box is checked I move the users to another table.I need to pass some parameter of or each row to a stored proc. My question. In the loop where I check if the checkbox is selected I need to call the stored procedure.Currently I do an open and closed inside the loop.What is best and most effficent method of doing this should I open and close the connection outside the loop and change the procs parameters as loop through. System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection( System.Configuration.ConfigurationManager.ConnectionStrings["connString"].ConnectionString); System.Data.SqlClient.SqlCommand commChk = new System.Data.SqlClient.SqlCommand("storedProc", conn); commChk.CommandType = System.Data.CommandType.StoredProcedure; commChk.Parameters.AddWithValue("@mUID", ddMainUser.SelectedValue.ToString()); commChk.Parameters.AddWithValue("@sUId", gvUsers.Rows[i].Cells[2].Text); commChk.Connection.Open(); commChk.ExecuteNonQuery(); conn.Close(); If so exactly how do I do this? How do I reset the parmaters for the proc? I haven't done this before where I need to loop through passing parameter to the same proc. thanks
Hi All, I'll admit that I'm not the greatest at stored procedure/functions but I want to learn as much as possible. So I have two questions: 1) I had VS2005 autogenerate a sqldatasource that created Select/Insert/Update stored procedures. When Updating a record and calling the stored procedure, I want to query another table (we'll call it tblBatchNo) that has only one record, Batchno. I want to put that current batchno into the Update statement and update the record with the current batchno. Can someone point me in the right direction? Remember that I'm still a beginner on this subject. 2) Can someone provide any links to online tutorials on t-sql? Thanks in advance. Curtis
Hi.....I have problem and I need your helpI stored a procedure in the Projects Folder in my computerand I want to return the procedure result in a column inside tableHow I can do that?????????thank you
Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
I'm using the sp_OAMethod method to call a method called "getDesc" from within a VB .dll I created. Within the .dll file, the method is called "getDesc()", but for some reason I'm getting an error saying it is an unknown name. I am able to create the object without errors, so I know the .dll is correctly registered and is being found by the server. Any idea what would cause this error when I know the method name is correct? The code I use is below (without error handling to make it shorter):
I am using an Access 2003 front-end, and an SQL backend to run my application. I have a pretty good handle on using stored procedures and assigning variables for criteria within the SPROC.
I am however having a problem when I try to use a variable in place of a named procedure. For example I have a function that runs 2 procedures, therefore I "Call" the function with the code that runs my procedure, and simply change the name of the SPROC with each call. My problem is that I cannot figure out the syntax to use a variable for the named procedure. My code always errors on the line "objConn.MySProc MyCalendar, objRs" because MySproc is of course not a named procedure.
So how do I refer to a procedures name using a variable?
Hi All,I have a stored procedure.I need to create a scheduled job using that stored procedure.I went to Enterprise Manager -->Management--> Jobs-->New JobPropertiesIn the step tab, I can select db and put the codes.Instead of writing the code there, I want to call the stored procedurein the command box.How can I do that?System: MS SQL Server 2000I would highly appreciate your help.Thanks a million in advance.Best regards,mamun
HelloI am calling a stored procedure in a MSDE/SQLServer DB form within myVisual C++ 6.0 program along the linesCCommand<CAccessor<CdboMyAccessor>>::Open(m_session, NULL);WithDEFINE_COMMAND(CdboMyAccessor, _T("{ CALL dbo.MyProc; 1(?,?) }"))It all works sweet as, but it can take a while and I want to let theuser abort it.Everything I've tried ends in tears.
