Hi All Please review this vb6 code (the stored procedure was added by aspnet_regsql.exe) Thanks Guy
With CMD .CommandText = "aspnet_Users_CreateUser" Call .Parameters.Refresh .Parameters(1).Value = "812cb465-c84b-4ac8-12a9-72c676dd1d65" .Parameters(2).Value = "myuser" .Parameters(3).Value = 0 .Parameters(4).Value = Now() Call RS.Open(CMD) End With The error I get is :Invalid character value for cast specification. This is the stored procedure code: set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[aspnet_Users_CreateUser] @ApplicationId uniqueidentifier, @UserName nvarchar(256), @IsUserAnonymous bit, @LastActivityDate DATETIME, @UserId uniqueidentifier OUTPUT AS BEGIN IF( @UserId IS NULL ) SELECT @UserId = NEWID() ELSE BEGIN IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId ) ) RETURN -1 END INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate) VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate) RETURN 0 END
I am trying to pass a value from one stored procedure into another. I have got the following stored procedures: Stored Procedure 1:ALTER PROCEDURE test2 (@Business_Name nvarchar(50) ) AS BEGINDECLARE @Client_ID INT SET NOCOUNT ON; INSERT INTO client_details(Business_Name) VALUES(@Business_Name) SELECT @Client_ID = scope_identity() IF @@NESTLEVEL = 1 SET NOCOUNT OFF
RETURN @Client_ID END Stored Procedure 2 - Taking Client_ID from the proecedure test2ALTER PROCEDURE dbo.test3 ( @AddressLine1 varchar(MAX), @Client_ID INT OUTPUT ) ASDECLARE @NewClient_ID INT EXEC test2 @Client_ID = @NewClient_ID OUTPUT INSERT INTO client_premises_address(Client_ID, AddressLine1) VALUES(@Client_ID, @AddressLine1) SELECT @NewClient_ID When I run this proecedure I get the following error: FailedProcedure or function 'test2' expects parameter '@Business_Name', which was not supplied. Cannot insert the value NULL into column 'Client_ID', table 'C:INETPUBWWWROOTSWWEBSITEAPP_DATASOUTHWESTSOLUTIONS.MDF.dbo.client_premises_address'; column does not allow nulls. INSERT fails. The statement has been terminated. However If I run Stored Procedure Test2 on its own it runs fine Can anyone help with this issue? Is there something that I have done wrong in my stored procedures? Also does anyone know If I can use the second stored procedure in a second webpage, but get the value that was created by running the stored proecdure in the previous webpage?
// add the input parameters and set their values cmd.Parameters.Add(new SqlParameter("@ContactName", SqlDbType.VarChar, 50)); cmd.Parameters["@ContactName"].Value = txbxContactName.Text;
CREATE PROCEDURE procCustomers @ContactName nvarchar(50) AS SELECT * FROM customers WHERE customers.contactname LIKE @ContactName ORDER BY customers.contactname ASC GO
no compile errors, it just returns nothing even if there is supposed to be a match... what am doing wrong?
Hi, i encounter a problem in the .NET. I have write the stored prodeure for the registering function and it works perfectly in the SQL Server. But when i called the stored procedure from the .NET, it cannot work. Can someone pls help? Thanks!
When called from .NET, it cannot works.. These are the codes:
Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Dim connStr As String = System.Configuration.ConfigurationSettings.AppSettings("SqlConnection.connectionString")
Dim dbConn As New SqlConnection dbConn.ConnectionString = connStr Try dbConn.Open() Dim dbCmd As New SqlCommand dbCmd.Connection = dbConn dbCmd.CommandType = CommandType.StoredProcedure dbCmd.CommandText = "spAddProfile"
Dim dbParam As SqlParameter dbParam = dbCmd.Parameters.Add("@return", SqlDbType.Int) dbParam.Direction = ParameterDirection.ReturnValue
I have a stored procedure that works on my development server but when I placed it on the server that is to be the prodcuction server i get the following response:
output ---------------- The name specified is not recognized as an internal or external command, operable program or batch file. This is the procedure:
CREATE PROCEDURE sp_OutputClaim @OutFile varchar(255), @CurAns char(8) AS
declare @CmdLine varchar(255)
select FieldX into ##TempTable from Table where FieldY = @CurAns
Msg 1008, Level 16, State 1, Procedure P_SEL_ALLPERSONAS, Line 13
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
I am not understanding this part of the problem. I am currently reusing a stored procedure that has a ".." as part of the select statement.
I can't put the select statement up here due to privacy but I have found the error where the error states the following:-
Invalid Column Prefix: AM, invalid table name.
I noticed that part of the select statement was the following:-
AM..Field1
I tried executing this stored procedure in the query analyzer and it works fine, but when I tried executing it in SSRS, it gives me the error. After searching through the internet for possible causes, I found out that it was the ".." was giving the error. Anyone knows why ? I found out that it was supposed to bypass any users and permissions to the table.
I was trying to create a simple SP that return a single value as follows:CREATE PROCEDURE IsListingSaved@MemberID INT,@ListingID INTASIF EXISTS (SELECT [Member_ID] FROM [Member_Listing_Link] WHERE [Member_ID] = @MemberID AND [Listing_ID] = @ListingID) Return 1ELSE Return 0GOWhen I try it out in the Tableadapter's preview table, I get the correct result (1, where the entries exist). However, in the BLL, I tried to get the value as:Dim intResult as IntegerintResult = CType(Adapter.IsListingSaved(intMemberID, intListingID), Integer). However, this always returns 0 (when it should be returning 1). P.S. Curiously, breakpoints skipped the VS generated code for the adapter. What could be the problem? Thanks,Wild Thing
All: I have created a stored procedure on SQL server that does an Insert else Update to a table. The SP starts be doing "IF NOT EXISTS" check at the top to determine if it should be an insert or an update. When i run the stored procedure directly on SQL server (Query Analyzer) it works fine. It updates when I pass in an existing ID#, and does an insert when I pass in a NULL to the ID#. When i run the exact same logic from my aspx.vb code it keeps inserting the data everytime! I have debugged the code several times and all the parameters are getting passed in as they should be? Can anyone help, or have any ideas what could be happening? Here is the basic shell of my SP: CREATE PROCEDURE [dbo].[spHeader_InsertUpdate] @FID int = null OUTPUT,@FLD1 varchar(50),@FLD2 smalldatetime,@FLD3 smalldatetime,@FLD4 smalldatetime AS Declare @rtncode int IF NOT EXISTS(select * from HeaderTable where FormID=@FID) Begin begin transaction --Insert record Insert into HeaderTable (FLD1, FLD2, FLD3, FLD4) Values (@FLD1, @FLD2, @FLD3,@FLD4) SET @FID = SCOPE_IDENTITY(); --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 1 return @rtncode end endELSE Begin begin transaction --Update record Update HeaderTable SET FLD2=@FLD2, FLD3=@FLD3, FLD4=@FLD4 where FormID=@FID; --Check for error if @@error <> 0 begin rollback transaction select @rtncode = 0 return @rtncode end else begin commit transaction select @rtncode = 2 return @rtncode end End---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General: Result Set: None Connection Type: OLE DB SourceType: Direct Input IsQueryStoredProcedure: False (this is greyed out and cannot be changed) Bypass Prepare: True
When I use the following execute statement where I am "Hard Coding" in the parameters, the stored procedure runs successfully and it places the data into the table per the stored procedure.
However, the @system_cd parameter can change, so I wanted to set these parameters up as variables and use the parameter mapping in the Execute SQL Task.
I have set this up as follows and it runs the package successfully but it does not put the data into the table. The only thing I can figure is either I have the variables set up incorrectly or the parameter mapping set up incorrectly.
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load] (@system_cd nvarchar(10), @location_type_cd nvarchar(10)) AS BEGIN .....................
Here is my set up, what is wrong here:
I Created these Variables:
Name Scope Data Type Value system_cd Locations String '03' location_type_cd Locations String Store
I added these parameter mappings in the Execute SQL Task
Variable Name Direction Data TypeParameter NameParameter Size User::system_cd Input NVARCHAR@system_cd -1 User::location_type_cd Input NVARCHAR@location_type_cd -1
I used this SQLStatement: EXEC dbo.sp_ml_location_load ?,
It runs the package successfully but it does not put the data into the table.
SELECT .... FROM temp LEFT OUTER JOIN anothertable ON ...
This stored procedure works fine in Management Studio.
I then use this stored procedure in an ASP.NET page via a SQLDataSource object. The ASP.NET code compiles without error, but the result returned is empty (my bounded GridView object has zero rows). From Web Developer, if I try to Refresh Schema on the SQLDATASource object, I get an error: "Invalid object name '#t1'. The error is at the red #1 as I tried putting something else at that location to confirm it.
What does the error message mean and what have I done wrong?
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 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.
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
For some reason my stored procedure is returning no results, when I know it should be. I am trying to take a textbox someone has typed into and returning all names from the database which include that string. My SQL statement is
ALTER PROCEDURE getLecturerNames ( @lName as varchar(20) ) AS SELECT * FROM Lecturers WHERE lName LIKE '%@lName%'
When I step into the procedure, the @lName parameter shows lName = 'ike' (what I typed to search) but no results are returned. However when I change the procedures last line to :
WHERE lName LIKE '%ike%'
2 records are returned. Is SQL server adding the quotation marks causing the problem, or if not what is?
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 have been looking at this for over a day now. I cannot see why this procedure does not work, its so simple. No matter what happens it always returns 0. If it locates a record, it doesnt update it, yet it still returns 0. It should not be returning 0 if its not updating so I can't figure out why it does. Why does this always return 0? [pre]Create Procedure CreateNewCategory @title nvarchar(100), @description nvarchar(1000), @displayOrder intAS DECLARE @Result as int IF EXISTS(SELECT categoryTitle FROM categories WHERE categoryTitle = @title) BEGIN SELECT @Result = 1 ENDELSE BEGIN INSERT INTO categories(categoryTitle, categoryDescription, displayOrder) VALUES(@title, @description, @displayOrder) /* If no error was encountered, 0 will be returned. */ SELECT @Result = @@Error ENDGO[/pre] Thanks!
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?
Wael writes "Hi all. i am using SQL server 2000 database and crystal reports XI. The purpose of this report is to measure the loyalty of the clients to buy and use a prepaid cards from a telephone company, the company concerned to know how it perform on a various range measures. in technical saying. we have 2 tables (CLL, CRD) the first one save a record about each call made by a certain card, including card no, source no(very important), destination no , date performed, duration and so on ... the second table contains information about card itself like card no, card price, type , pricing category, time open, date closed (very important), brand, and so on.... as i mentioned there is only 2 fields in concern in our report. *the first is the source no as an indication of a client (assume that the client will always call from his home to outer line or whatever .... ) *the second is the date closed as it says that a certain card finishes its value at this date, it will be the base date to build your periods on.
the report should do the following:- 1- prompt the user for a period range to analyze. 2- prompt the user to specify how this range will be analyzed (yearly, quarterly, monthly, weekly, ....). 3- cut the range into slices as determined in the second period. 4- for each period, compare it with the previous periods (as whole from the start of the range to just before the current period) to get the following 3 counts: - new clients : didn't call at the previous period but called in the current period. - existing clients : called in previous period and still calling in the current period. - left clients : called in previous period and didn't in the current period . 5- and so on till the last period in the range. 6- this data preferred to be displayed graphically, if not as a crosstab. 7- note that : i said certain client called in a certain period , if he used a card and call at least one time in a that period.
please know that i am a new bie in stored procedures, so if there is a possibility for that report to run with stored procedure, i will be grateful if it is in some details. at the end , i am so sorry for that detailed explanation, but i want to clarify my poit. please any help, i will appreciate it.
I had try calling a function, that call a store procedure, repeatly using a for loop, but I notice it will only get the expected part_id in the first time, and return an empty string sub-sequentially without throwing an exception. So I had try using a sql query instead, but the same thing happen. Below is my function, can you point out to me what's wrong? My original version that calls a store procedure Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String Dim mySqlCommand As New SqlCommand Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString()) Dim myPart_id As String mySqlCommand.CommandType = CommandType.StoredProcedure mySqlCommand.CommandText = "getPartId" mySqlCommand.Connection = mySqlConnection mySqlCommand.Parameters.Add(New SqlParameter("@part_supplierserialnumber", part_supplierserialnumber)) Try mySqlConnection.Open() myPart_id = mySqlCommand.ExecuteScalar() Catch ex As Exception myPart_id = "" Finally mySqlConnection.Close() mySqlConnection.Dispose() End Try Return myPart_id End Function My Store procedure create procedure getPartId@part_supplierserialnumber as nvarchar(50)as select top 1 part_id from tblPtSingapore where part_supplierserialnumber = @part_supplierserialnumber order by part_datecreated desc
GO The new version I tried which happen the same thing Public Shared Function getPartId(ByVal part_supplierserialnumber As String) As String Dim myPart_id As String Dim strSql As String = "select top 1 part_id from tblPtSingapore where part_supplierserialnumber = '" & part_supplierserialnumber & "' order by part_datecreated desc" Dim mySqlConnection As SqlConnection = New SqlConnection(GetERATSConnectionString()) Dim mySqlCommand As New SqlCommand(strSql, mySqlConnection) Try mySqlConnection.Open() myPart_id = mySqlCommand.ExecuteScalar() Catch ex As Exception myPart_id = "" Finally mySqlConnection.Close() mySqlConnection.Dispose() End Try Return myPart_id End Function
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.
I was wondering if SQL Cache Dependency would be in fact invalidated if: 1. it was created based on a procedure type command. 2. if the select statement retrieves the data from multiple database tables Any help would be more appreciated. I am stuck with the fact that none of the data bases on sql dependency is invalidated. I spent literally hours to understand what i am doing incorrectly.
Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'. USE [merck_acronyms] GO
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
