Using Sp_columns Stored Procedure Trouble

May 12, 2005

Hello,

I'm trying to use the "sp_columns" stored procedure to pull in some
information about a table in my db, but I'm continuing to get the same
error:

ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure
'sp_columns' expects parameter '@table_name', which was not supplied.

I'm not sure why, but I've re-written my code several times and can't
figure out just why this error is happening.  Here's a snippet of
the code:

Private Function GetDataType(ByVal columnName As String, ByVal table As String) As String
        Dim con As New
OdbcConnection(ConfigurationSettings.AppSettings.Get("LiquorLiabilityConnection"))
        Dim com As New OdbcCommand("sp_columns", con)
        com.CommandType = CommandType.StoredProcedure

        Dim param As New OdbcParameter("@table_name", table)
        param.OdbcType = OdbcType.VarChar
        com.Parameters.Add(param)

        param = New OdbcParameter("@column_name", columnName)
        param.OdbcType = OdbcType.VarChar
        com.Parameters.Add(param)

        Dim dr As OdbcDataReader
        Dim name As String

        con.Open()
        dr = com.ExecuteReader() 'THIS TRIGGERS THE ERROR
        While dr.Read
            name = dr("TYPE_NAME")
        End While
        dr.Close()
        con.Close()

        Return name
    End Function

Any suggestions would be appreciated.

View 3 Replies


ADVERTISEMENT

Trouble With Stored Procedure

Feb 4, 2008

When I try to run these queries in my stored procedure by hardcoding officeids, they run fine, but when I use the parameter @officeid and then try to run the procedure by using
getEmployeeInfo_proc '001'   I get no results. Can anyone see what I could be doing wrong?  Thanks Here is my code:
 
 1 SET QUOTED_IDENTIFIER ON
2 GO
3 SET ANSI_NULLS ON
4 GO
5
6
7 ALTER PROCEDURE getEmployeeInfo_proc (@officeid varchar(10))AS
8 select distinct
9 a.emplid, a.name,a.last_name, a.first_name, a.status, a.officeid,
10 b.deptid_child_node, b.dept_child_descr, a.job_descr,
11 d.officename
12 from employeeinfo_vie a,
13 dept_all_nodes_tbl b,
14 office_tbl d
15 where
16 a.status in ('A','L','P') and
17 a.officeid = d.officeid and
18 a.deptid = b.deptid_child_node and
19 a.officeid = '@officeid'
20
21 UNION
22
23 select distinct
24 e.emplid, e.name, e.last_name, e.first_name, e.status, e.officeid,
25 b.deptid_child_node, b.dept_child_descr, f.job_descr,
26 d.officename
27 from phone_admin_tbl e,
28 dept_all_nodes_tbl b,
29 office_tbl d,
30 jobs_tbl f
31 where
32 e.status in ('A','L','P') and
33 e.officeid = d.officeid and
34 e.deptid = b.deptid_child_node and
35 e.job_code = f.job_code and
36 e.officeid = '@officeid'
37 order by name
38
39 return
40
41 GO
42 SET QUOTED_IDENTIFIER OFF
43 GO
44 SET ANSI_NULLS ON
45 GO
46
47
48
49
50
 

View 4 Replies View Related

Trouble With Stored Procedure

Mar 11, 2008

I'm having trouble with this stored procedure, it gets down to the insert and then times out.
Can anyone see why the insert might be wrong?
I did check and at least one of the workshops is open and the @Status is showing it as Open on the client.ALTER PROCEDURE [dbo].[AddNewWorkshopAssigned]

@Workshop_ID int,
@Client_ID int,
@Wait_list bit,
@DateEntered datetime,
@EnteredBy nvarchar(50),

@Status nvarchar(10) OUTPUT

AS

BEGIN

SET @Status = (SELECT
CASE
WHEN (tblWorkshop.Workshop_Status) = 'Canceled' THEN 'Canceled'
WHEN (tblWorkshop.Workshop_Size - COALESCE(COUNT(tblWorkshopsAttended.client_ID),0)) >= 1 THEN 'Open'
ELSE 'Closed' END AS Current_Status
FROM tblWorkshop INNER JOIN
tblWorkshopsAttended ON tblWorkshop.Workshop_ID = tblWorkshopsAttended.workshop_id
WHERE tblWorkshopsAttended.Workshop_ID=@Workshop_ID AND tblWorkshopsAttended.Wait_list = 0
GROUP BY tblWorkshop.Workshop_ID,tblWorkshop.Workshop_Size,tblWorkshop.Workshop_Status)

--Change Workshop Status if NOT canceled.
If @Status <> 'Canceled'
UPDATE tblWorkshop SET
Workshop_Status=@Status
WHERE tblWorkshop.Workshop_ID=@Workshop_ID

IF @Status = 'Open'

INSERT INTO tblWorkshopsAttended (Workshop_ID,Client_ID,Wait_list,DateEntered,EnteredBy)

Values (@Workshop_ID,@Client_ID,@Wait_list,@DateEntered,@EnteredBy)

END 

View 3 Replies View Related

Syntax Trouble With Int In Stored Procedure

May 9, 2006

Dear Forum,
I am adding a new column name to my Stored Procedure called HeadlinerID.  It is an Int that is 4 characters long.  I seem to be putting this in incorrectly in my stored procedure.  I have tried it like: @HeadlinerID int(4), and @HeadlinerID int,  and both ways I get the error below:
Error 170: Line 16: Incorrect Syntax near ‘)’. Line 40: Incorrect syntax near ‘@Opener’.
Is there a trick to putting in integers in a stored procedure?
 
Thanks,
Jeff Wood
Boise, ID
CREATE PROCEDURE Item_Insert(   @Title varchar(50),   @_Date datetime,   @Venue varchar(50),   @HeadlinerID int(4),   @Opener varchar(150),   @Doorstime varchar(50),   @Showtime varchar(50),   @Price varchar(50),   @Onsaledate datetime,   @Ticketvendor varchar(50),   @TicketURL varchar(150),   @Description varchar(1000),
)AS
INSERT INTO shows(   Title,   _Date,   Venue,   HeadlinerID,   Opener,   Doorstime,   Showtime,   Price,   Onsaledate,   Ticketvendor,   TicketURL,   Description)VALUES(   @Title,   @_Date,   @Venue,   @HeadlinerID,   @Opener,   @Doorstime,   @Showtime,   @Price,   @Onsaledate,   @Ticketvendor,   @TicketURL,   @Description    )GO

View 3 Replies View Related

Trouble With Passing Values To Stored Procedure

Feb 13, 2008

Hi All, I'm a newbie learning windows applications in visual basic express edition, am using sqlexpress 2005 So i have a log in form with username and password text fields.the form passes these values to stored procedure 'CheckUser' Checkuser then returns a value for groupid. If its 1 they are normal user, if its 2 its admin user. Then opens another form called Organisations, and closes the log in form. However when i run the project, and enter a username and password and press ok ti tells me that there is incorrect syntax beside a line. I have no idea, and I'm sure that there is probably other things wrong in there. here is the code for the login button click event:  Public Class Login Dim connString As String = "server = .SQL2005;" & "integrated security = true;" & "database = EVOC"
'Dim connString As String = _ '"server = .sqlexpress;" _ '& "database = EVOC;" _ '& "integrated security = true;"




Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login_Log_In.Click ' Create connection
Dim conn As SqlConnection = New SqlConnection(connString) ' Create command

Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = conn cmd.CommandText = "CheckUser"

Dim inparam1 As SqlParameter = cmd.Parameters.Add("@Username", SqlDbType.NVarChar) inparam1.Value = Login_Username.ToString Dim inparam2 As SqlParameter = cmd.Parameters.Add("@Password", SqlDbType.NVarChar) inparam2.Value = Login_Password.ToString inparam1.Direction = ParameterDirection.Input inparam2.Direction = ParameterDirection.Input 'Return value
Dim return_value As SqlParameter = cmd.Parameters.Add("@return_value", SqlDbType.Int) return_value.Direction = ParameterDirection.ReturnValue cmd.Connection.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader Dim groupID As Integer

groupID = return_value.Value

If groupID < 0 Then
MessageBox.Show("Access is Denied") Else Dim Username = Me.Login_Username Dim org As New Organisations org.Show() End If

conn.Close()



End Sub The stored procedure code is ok as i know it works as it should, but if it helps the code is: ALTER PROCEDURE [dbo].[CheckUser] -- Add the parameters for the stored procedure here
@UserName nvarchar(50) = N'', @Password nvarchar(50) = N''
ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
IF (SELECT COUNT(*) FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)=1BEGINPRINT 'User ' + @Username + ' exists'
SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@PasswordRETURN (SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)ENDELSE BEGINPRINT 'User ' + @Username + ' does not exist'
RETURN (-1)ENDEND
 All help greatly appreciated to get me past this first hurdle in my first application!! CheersTom  

View 16 Replies View Related

Trouble Updating SQLTable Using A Stored Procedure

Jan 30, 2004

If I run a update stored procedure on my SQLServer It work Fine.
But When I try it in my VB code, it's just do nothing not even an error message.

What I've got to do for being able to Update SQLTable with a stored procedure?

That's my VB code:


Dim objConnect As SqlConnection
Dim strConnect As String = System.Configuration.ConfigurationSettings.AppSettings("StringConn")

objConnect = New SqlConnection(strConnect)

Dim objCommand As New SqlCommand("internUpdate", objConnect)
objCommand.CommandType = CommandType.StoredProcedure

Try
Dim objParam As SqlParameter

objParam = objCommand.Parameters.Add("Id", SqlDbType.Int)
objParam.Direction = ParameterDirection.Input
objParam.Value = InternIDValue

objParam = objCommand.Parameters.Add("Address", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Address.Value.Trim()

objParam = objCommand.Parameters.Add("City", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = City.Value.Trim()

objParam = objCommand.Parameters.Add("ProvinceCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myProvince.SelectedValue

objParam = objCommand.Parameters.Add("PostalCode", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = PostalCode.Value.Trim()

objParam = objCommand.Parameters.Add("Phone", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone1.Value.Trim()

objParam = objCommand.Parameters.Add("Phone2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = Phone2.Value.Trim()

objParam = objCommand.Parameters.Add("Email", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress1.Value.Trim()

objParam = objCommand.Parameters.Add("Email2", SqlDbType.VarChar, 50)
objParam.Direction = ParameterDirection.Input
objParam.Value = EmailAddress2.Value.Trim()

objParam = objCommand.Parameters.Add("EmploymentStatusCode", SqlDbType.Char, 2)
objParam.Direction = ParameterDirection.Input
objParam.Value = myStatus.SelectedValue

objConnect.Open()
objCommand.ExecuteNonQuery()
objConnect.Close()

Catch ex As Exception
Exit Sub
End Try



Thanks!!

View 1 Replies View Related

Trouble With Stored Procedure (Invalid Object Name)

Mar 31, 2005

I am having a bit of trouble with a stored procedure on the SQL Server that my web host is running.
The stored procedure I have created for testing is a simple SELECT statement:
SELECT * FROM table
This code works fine with the query tool in Sqlwebadmin. But using that same code from my ASP.NET page doesn't work, it reports the error "Invalid object name 'table'". So I read a bit more about stored procedures (newbie to this) and came to the conslusion that I need to write database.dbo.table instead.
But after changing the code to SELECT * FROM database.dbo.table, I get the "Invalid object name"-error in Sqlwebadmin too.
The name of the database contains a "-", so I write the statements as SELECT * FROM [database].[dbo].[table].
Any suggestions what is wrong with the code?
I have tried it locally with WebMatrix and MSDE before I uploaded it to the web host and it works fine locally, without specifying database.dbo.

View 2 Replies View Related

Trouble Formatting Strings In Email Stored Procedure

Aug 9, 2007

Hello,I have a sqlserver stored procedure that calls the stored procedure sp_send_cdosysmail_htm to send reminder emails to customers. I am experiencing problems when trying to concatenate the id being renewed in the subject field. I'm always getting the message "error 170: line 10: Incorrect syntax near '+'."Does anyone know what the error means or can point me to a resource on the web? Many thanksRitao  CREATE PROCEDURE dbo.SendRenewalEmail         @ID INT AS         BEGIN                 exec dbo.sp_send_cdosysmail_htm                         @From = 'yosemite.sam@acme.com',                         @To = 'road.runner@acme.com',                         @Cc = null,                         @BCC = null,                         @Subject =  'Order  '  + @ID + 'Renewal Reminder',                         @Body = 'Hello roadrunner....'         ENDGO 

View 4 Replies View Related

Trouble Accessing SQL Server 2005 Stored Procedure Parameters

Jun 14, 2007

I created a stored procedure (see snippet below) and the owner is "dbo".
I created a data connection (slcbathena.SLCPrint.AdamsK) using Windows authentication.
I added a new datasource to my application in VS 2005 which created a dataset (slcprintDataSet.xsd).
I opened up the dataset in Designer so I could get to the table adapter.
I selected the table adapter and went to the properties panel.
I changed the DeleteCommand as follows: CommandType = StoredProcedure; CommandText = usp_OpConDeleteDirectory. When I clicked on the Parameters Collection to pull up the Parameters Collection Editor, there was no parameters listed to edit even though there is one defined in the stored procedure. Why?

If I create the stored procedure as "AdamsK.usp_OpConDeleteDirectory", the parameters show up correctly in the Parameters Collection Editor. Is there a relationship between the owner name of the stored procedure and the data connection name? If so, how can I create a data connection that uses "dbo" instead of "AdamsK" so I can use the stored procedure under owner "dbo"?



Any help will be greatly appreciated!



Code SnippetCREATE PROCEDURE dbo.usp_OpConDeleteDirectory
(
@DirectoryID int
)
AS
SET NOCOUNT ON
DELETE FROM OpConDirectories WHERE (DirectoryID = @DirectoryID)

View 1 Replies View Related

Trouble Writing Stored Procedure To Retrieve Records By Selected Month And Year

May 15, 2007

hi,
i am a nubie, and struggling with the where clause in my stored procedure. here is what i need to do:
i have a gridview that displays records of monthly view of data. i want the user to be able to page through any selected month to view its corresponding data. the way i wanted to do this was to set up three link buttons above my gridview:
[<<Prev]  [Selected Month]  [Next>>]
 the text for 'selected month' would change to correspond to which month of data was currently being displayed in the gridview (and default to the current month when the application first loads).  
i am having trouble writing the 'where' clause in my stored procedure to retrieve the selected month and year.
i am using sql server 2000. i read this article (http://forums.asp.net/thread/1538777.aspx), but was not able to adapt it to what i am doing.
i am open to other suggestions of how to do this if you know of a cleaner or more efficient way. thanks!

View 2 Replies View Related

Sp_columns Not Returnign Anything

Oct 13, 2006

Greetings all. I have a bizarre problem. I'm using sp columns to generate an html table with the field information of certain tables in my database. The table names are stored in an SQL Table called 'Manage_Tables' and I have 9 tables shown there. 6 out of the 9 are displaying correctly but the other 3 arent and the problem is coming from teh sp columns procedure that is returning nothing. why does this happen? all the tables are the same and have been created on teh same day by the same user with the rame rights and I have admin rights. Any clue?

View 1 Replies View Related

How To Get One Column From Sp_columns

Feb 27, 2008

Hi,
When I run sp_columns @table_name = someTable, I get many columns like TABLE_QUALIFIER, TABLE_OWNER and so on.
Is there a easy way to get only a COLUMN_NAME column?
Thanks 

View 2 Replies View Related

Underscore & Sp_columns ?!!

Oct 9, 2000

Hi friends,

I got troubles when I tried to pass table name to sp_columns via
@local_variable:

declare @tname as char(50)
set @tname = 'TB_MyTable'
exec sp_columns @tname

result of such script is:

TABLE_QUALIFIER ...[skipped]
-----------------------------------
(0 row(s) affected)

But if I will say:

exec sp_columns 'TB_MyTable'

result will be correct (all neccessary data about columns will be provided)

So, looks like if I pass table name which contains underscore via
@local_variable or SP parameter - result will be wrong.
If table name won't contains underscore - everything works fine and result
of script:

declare @tname as char(50)
set @tname = 'MyTable'
exec sp_columns @tname

will be absolutely correct.

Please, anybody can clarify this situation ???

Thanks in advance,

Michael

View 2 Replies View Related

Sp_columns Into A Variable

Mar 23, 2004

Hello,
I'm using sp_columns and would like to stock the result recordset into a variable (or simply get only the column name into a table variable in sql)

Is there any way to select only the column name and put it into a table variable ?

Thank

View 2 Replies View Related

Sp_columns Slow

Dec 3, 2007

In SQL 2005, I'm getting very slow response times with sp_columns. It takes
around 8 seconds to return the results of calling sp_columns.

Any ideas?

Thanks

-Dave

View 3 Replies View Related

Store Return Result From Sp_columns

Oct 31, 2007

I excute sp_columns in my Stored Procedure script to get the data type of a table column.
EXEC sp_columns @table_name = 'XXX', @column_name='YYY'
How do i store the column 'TYPE_NAME' in the return row into a variable so that i can use it later in my stored procedure?
Thanks
Hannah

View 1 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

Export Sp_columns Result Into A Temp Table?

Mar 1, 2004

Can anyone show me the syntax for exporting the result of sp_columns into a new table?

I've tried using "SELECT EXEC sp_columns...INTO 'column_info'" but I keep getting syntax errors that are unclear to me.

Any help would be greatly appreciated.

ab

View 3 Replies View Related

Trouble With Stored Procedured

Nov 19, 2003

Hello,

I´m tring to create a stored procedure in SQLSERVER 2000 that receives as input parameter a code from some table. I would like to return (as output) in this stored procedure all information from this table that was linked to this code.

Example: in other words, to explaim better I would like to transforme the following SQL to stored procedured:

SELECT * FROM VENDEDOR WHERE codVendedor = '20'

considering that in this table 'VENDEDOR' we have: cod, name, address, ..... from VENDEDOR.

Is there a way to do this?

Best Regards,

Gustavo M R

View 3 Replies View Related

Sp_columns In SQL2K5 Gives No Rows For Function That Return Table

May 9, 2006

Some automated tools use sp_columns to find out the columns for a table, view or UDF that returns table.

In SQL Server 2000 it gives columns back but in SQL Server 2005 it does not, compat level set at 80 and 90.

Does anyone have an idea what id going on here?

Repro script below. I expect the script to return information about the two columns in the table defined in fnTestColumnsFromFunctions().

if exists (select 1
from sysobjects
where id = object_id('dbo.fnTestColumnsFromFunctions')
and type in ('IF', 'FN', 'TF'))
drop function dbo.fnTestColumnsFromFunctions;
go

create function dbo.fnTestColumnsFromFunctions()
returns @TestTable table
(
ttID int,
ttName varchar(50)
)
as
begin
return;
end
go

declare @dbname sysname;
declare @n int;
set @dbname = db_name();
exec @n = dbo.sp_columns
@table_name = 'fnTestColumnsFromFunctions',
@table_owner = 'dbo',
@table_qualifier = @dbname,
@column_name = null,
@ODBCVer = 2;

if exists (select 1
from sysobjects
where id = object_id('dbo.fnTestColumnsFromFunctions')
and type in ('IF', 'FN', 'TF'))
drop function dbo.fnTestColumnsFromFunctions;
go

View 7 Replies View Related

Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?

Mar 3, 2008

Hi all,

I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):

(1) /////--spTopSixAnalytes.sql--///

USE ssmsExpressDB

GO

CREATE Procedure [dbo].[spTopSixAnalytes]

AS

SET ROWCOUNT 6

SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName

FROM LabTests

ORDER BY LabTests.Result DESC

GO


(2) /////--spTopSixAnalytesEXEC.sql--//////////////


USE ssmsExpressDB

GO
EXEC spTopSixAnalytes
GO

I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////

Public Class Form1

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")

Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)

sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure

'Pass the name of the DataSet through the overloaded contructor

'of the DataSet class.

Dim dataSet As DataSet ("ssmsExpressDB")

sqlConnection.Open()

sqlDataAdapter.Fill(DataSet)

sqlConnection.Close()

End Sub

End Class
///////////////////////////////////////////////////////////////////////////////////////////

I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)

Please help and advise.

Thanks in advance,
Scott Chang

More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.




View 11 Replies View Related

Extended Stored Proc Trouble With Srv_paramsetoutput And LOB Types

Jun 26, 2007

Hi,

I have written an extended stored proc: xp_DoSomething that can be called from T-SQL:






Code Snippet

declare @txt varchar(max)

exec xp_DoSomething @txt output

select @txt



The proc generates a correct value for @txt, but I can't get the result into the output parameter - the important part of the code is:






Code Snippet

if(FAIL == srv_paramsetoutput(srvproc, 1, dataPtr, len, FALSE))

{

throw OutputParameterDataError();

}



It seems that srv_paramsetoutput function always returns FAIL if type of the output parameter is LOB (varchar(max),nvarchar(max), varbinary(max)). Is there any way to return LOB parameter by an extended procedure?



Any help gratefully received



Thanks,

Va1era

View 2 Replies View Related

T-SQL (SS2K8) :: One Stored Procedure Return Data (select Statement) Into Another Stored Procedure

Nov 14, 2014

I am new to work on Sql server,

I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.

Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.

View 1 Replies View Related

SQL Server 2014 :: Embed Parameter In Name Of Stored Procedure Called From Within Another Stored Procedure?

Jan 29, 2015

I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?

CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],

[Code] ....

View 9 Replies View Related

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.

Sep 19, 2006

I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that? Articles, code samples, etc???

View 1 Replies View Related

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page

Dec 28, 2005

I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View 9 Replies View Related

SQL Server 2012 :: Executing Dynamic Stored Procedure From A Stored Procedure?

Sep 26, 2014

I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure

at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT

I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT

View 3 Replies View Related

System Stored Procedure Call From Within My Database Stored Procedure

Mar 28, 2007

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?

Thanks in advance

View 9 Replies View Related

Ad Hoc Query Vs Stored Procedure Performance Vs DTS Execution Of Stored Procedure

Jan 23, 2008



Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio

What could explain this?

Obviously,

All three scenarios are executed against the same database and hit the exact same tables and indices.

Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).

The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes

View 1 Replies View Related

User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net

Sep 13, 2007

Hi all,



I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.



Whenever I tried to right click stored procedure and select step into store procedure> i get following error



"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"



I am not sure what needs to be done on sql server side



We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.

Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?



Please advise..

Thank You

View 3 Replies View Related

Is The Transaction Context Available Within A 'called' Stored Procedure For A Transaction That Was Started In Parent Stored Procedure?

Mar 31, 2008

I have  a stored procedure 'ChangeUser' in which there is a call to another stored procedure 'LogChange'. The transaction is started in 'ChangeUser'. and the last statement in the transaction is 'EXEC LogChange @p1, @p2'. My questions is if it would be correct to check in 'LogChange' the following about this transaction: 'IF @@trancount >0 BEGIN Rollback tran' END Else BEGIN Commit END.
 Any help on this would be appreciated.

View 1 Replies View Related

Calling Stored Procedure Fromanother Stored Procedure

Oct 10, 2006

Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames

View 16 Replies View Related

Use Resultset Returned From A Stored Procedure In Another Stored Procedure

Nov 15, 2006

I have a store procedure (e.g. sp_FetchOpenItems) in which I would like to call an existing stored procedure (e.g. sp_FetchAnalysts). The stored proc, sp_FetchAnalysts returns a resultset of all analysts in the system.
I would like to call sp_FetchAnalysts from within sp_FetchOpenItems and insert the resultset from sp_FetchAnalysts into a local temporary table. Is this possible?
 Thanks,
Kevin

View 3 Replies View Related







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