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()
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
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]
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)
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?
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
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.
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
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)
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!
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?
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
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
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 ?
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
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?
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.
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
if exists (select 1 from sysobjects where id = object_id('dbo.fnTestColumnsFromFunctions') and type in ('IF', 'FN', 'TF')) drop function dbo.fnTestColumnsFromFunctions; 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)
'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.
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?
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],
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)
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
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?
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
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?
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.
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
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