Stored Procedure That Retrieve Object Name

May 10, 2004

Hi to all!
Is there a system stored procedure that retrieve info or name about an object?
I must know if a database contains a specific table and i must know if there is a specific DTS.. before execute it..
someone could help me??

thx a lot!

Ale.
Sorry for my poor english!!


bye! =)

View 2 Replies


ADVERTISEMENT

Retrieve Stored Procedure

Jan 15, 2007

I need some help retrieving the stored procedure listed below.  I would like to use a drop list to select the variable "UserName" and textboxes to send the variables "StartingDate" and "EndingDate".  How do I pass these variables and then have the results show up in a gridview?  Any advice would be greatly appreciated.  CREATE     PROCEDURE [dbo].[aspnet_starterkits_GetTimeEntryUserReportByUserNameAndDates] @UserName NVARCHAR(256), @StartingDate           datetime, @EndDate          datetimeASDECLARE @UserId AS UNIQUEIDENTIFIERSET NOCOUNT ONSELECT @UserId=UserId FROM aspnet_users WHERE UserName=@UserNameSELECT @UserName as UserName, SUM  (timeentryDuration) AS TotalDuration,SUM  (timeentryOvertime) AS TotalOvertimeHoursFROM aspnet_starterkits_TimeEntryWHERE aspnet_starterkits_TimeEntry.TimeEntryUserId=@UserId AND TimeEntryDate between @StartingDate and @EndDate

View 1 Replies View Related

Retrieve Data From A Stored Procedure

Jul 27, 2004

Hello Group
I am new to stored procedures and I have been fighting this for a while and I hope someone can help. In my sp it checks username and password and returns an integer value. I would like to retrieve some data from the same database about the user (the users first and last name and the password of the user). I can’t retrieve the data. Here is my code.
CREATE PROCEDURE stpMyAuthentication
(
@fldUsername varchar( 50 ),
@fldPassword Char( 25 )--,
--@fldFirstName char( 30 ) OUTPUT,
--@fldLastName char( 30 ) OUTPUT
)
As
DECLARE @actualPassword Char( 25 )
SELECT
@actualPassword = fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
IF @actualPassword IS NOT NULL
IF @fldPassword = @actualPassword
RETURN 1
ELSE
RETURN -2
ELSE
RETURN -1

SELECT
fldFirstName,
fldLastName,
fldPassword
FROM
tbMembUsers
Where
fldUsername = @fldUsername
GO
############### login page ################
Sub Login_Click(ByVal s As Object, ByVal e As EventArgs)
If IsValid Then
If MyAuthentication(Trim(txtuserID.Text), Trim(txtpaswrd.Text)) > 0 Then
FormsAuthentication.RedirectFromLoginPage(Trim(txtuserID.Text), False)
End If
End If
End Sub

Function MyAuthentication(ByVal strUsername As String, ByVal strPassword As String) As Integer
Dim myConn As SqlConnection
Dim myCmd As SqlCommand
Dim myReturn As SqlParameter
Dim intResult As Integer
Dim sqlConn As String
Dim strFirstName, strLastName As String

sqlConn = ConfigurationSettings.AppSettings("sqlConnStr")
myConn = New SqlConnection(sqlConn)

myCmd = New SqlCommand("stpMyAuthentication", myConn)
myCmd.CommandType = CommandType.StoredProcedure

myReturn = myCmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
myReturn.Direction = ParameterDirection.ReturnValue

myCmd.Parameters.Add(Trim("@fldUsername"), Trim(strUsername))
myCmd.Parameters.Add(Trim("@fldPassword"), Trim(strPassword))
myCmd.Parameters.Add("@fldFirstName", strFirstName)
myCmd.Parameters.Add("@fldLastName", strLastName)
myCmd.Parameters.Add("@fldPassword", strPassword)

myConn.Open()
myCmd.ExecuteNonQuery()
intResult = myCmd.Parameters("RETURN_VALUE").Value

myConn.Close()

'If strPassword = 55555555 Then
' Session("intDefaultPass") = 1
'End If

Session("strFullName") = strFirstName & " " & strLastName
Session("strPassword") = strPassword

If intResult < 0 Then
If intResult = -1 Then
lblMessage.Text = "Username Not Registered!<br><br>"
Else
lblMessage.Text = "Invalid Password!<br><br>"
End If
End If

Return intResult
End FunctionAt this time I am not getting any errors. How can I retrieve the data that I am after?
Michael

View 4 Replies View Related

Retrieve A Dataset From Stored Procedure

Apr 8, 2006

Hi, Can anyone please help me solve this problem.
My functions works well with this stored procedure:
CREATE PROCEDURE proc_curCourseID@studentID int ASSELECT * FROM StudentCourse WHERE mark IS NULL AND studentID = @studentID AND archived IS NULLGO
But when I applied the same function to the following stored procedure
CREATE PROCEDURE proc_memberDetails@memberID int ASSELECT * FROM member WHERE id = @memberIDGO
I received this message
Exception Details: System.Data.SqlClient.SqlException: Procedure or function proc_memberDetails has too many arguments specified.Source Error:



Line 33: SqlDataAdapter sqlDA = new SqlDataAdapter();
Line 34: sqlDA.SelectCommand = sqlComm;
Line 35: sqlDA.Fill(dataSet);
Line 36:
Line 37: return dataSet;
The function I am using is returning a DataSet as below:
public DataSet ExecuteStoredProcSelect (string sqlProcedure, ArrayList paramName, ArrayList paramValue)
{
      DataSet dataSet = new DataSet();
      SqlConnection sqlConnect = new SqlConnection(GetDBConnectionString());
      SqlCommand sqlComm = new SqlCommand (sqlProcedure, sqlConnect);
      sqlComm.CommandType = CommandType.StoredProcedure;

      for (int n=0; n<paramName.Count; n++)
      {
            sqlComm.Parameters.Add(paramName[n].ToString(),Convert.ToInt32(paramValue[n]));
      }

      SqlDataAdapter sqlDA = new SqlDataAdapter();
      sqlDA.SelectCommand = sqlComm;
      sqlDA.Fill(dataSet);
      return dataSet;
}
If this is not the correct way, is there any other way to write a function to return a dataset as the result of the stored procedure?
Thanks.

View 1 Replies View Related

Trying To Retrieve A Value Returned By A Stored Procedure

Apr 30, 2006

I have a stored procedure that return either 1 or -1 : IF EXISTS( ) BEGIN return 1 ENDELSE BEGIN return -1 ENDAnd then I try to retrieve the returned value by doing this:int value = Convert.ToInt32(command.ExecuteScalar());But the value I get is always 0. Anyone see what is wrong?  

View 1 Replies View Related

Retrieve Output Parameter From Stored Procedure

May 18, 2007

Hi,
 I'm having problems retrieving the output parameter from my stored procedure, i'm getting the following error
An SqlParameter with ParameterName '@slideshowid' is not contained by this SqlParameterCollection code as follows:
int publicationId = (int) Session["PublicationId"];string title = txtTitle.Text;int categoryid = Convert.ToInt32(dlCategory.SelectedItem.Value);SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);SqlCommand myCommand = new SqlCommand("sp_be_addSlideshow", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add(new SqlParameter("@publicationid", SqlDbType.Int));myCommand.Parameters["@publicationid"].Value = publicationId;myCommand.Parameters.Add(new SqlParameter("@title", SqlDbType.NVarChar));myCommand.Parameters["@title"].Value = title;myCommand.Parameters.Add(new SqlParameter("@categoryid", SqlDbType.Int));myCommand.Parameters["@categoryid"].Value = categoryid;myConnection.Open();myCommand.ExecuteNonQuery();string slideshowId = myCommand.Parameters["@slideshowid"].Value.ToString();myConnection.Close();
 my stored procedure:
CREATE PROCEDURE sp_be_addSlideshow( @publicationid int, @title nvarchar(50), @categoryid int, @slideshowid int = NULL OUTPUT)AS
INSERT INTO Slideshow(publicationid,title,slideshowcategoryid,deleted)
VALUES (@publicationid,@title,@categoryid,0)
SELECT @slideshowid = SCOPE_IDENTITY()GO

View 1 Replies View Related

Stored Procedure To Retrieve UserId From Aspnet_Users

Jan 17, 2008

Im a bit confused on my programming skills.  I know how to add a user and password using the Membership.CreateUser class.    ' Define database connection
Dim conn As New SqlConnection("Server=localhostSqlExpress;" & _ "Database=lnp;Integrated Security=True") ' Create command
Dim comm As New SqlCommand( _ "SELECT oldLogId, oldPassword FROM userDetails WHERE (oldLogId = N'chuck') OR (oldLogId = N'top_dawg')", conn) ' Open connection
conn.Open()
' Execute the command
Dim reader As SqlDataReader = comm.ExecuteReader()
' Do something with the data
While reader.Read()
Dim partPassword = reader.Item("oldPassword") ' Adds underscore character and first 3 letters of password for distinct USER ID
Dim newUserID = reader.Item("oldLogId") & "_" & partPassword.SubString(0, 3) ' Create User if Valid
Try
Membership.CreateUser(newUserID, partPassword)
Catch ex As MembershipCreateUserException
' Code that is executed when an exception is generated ' The exception's details are accessible through the ex object
usersLabel.Text &= "<p><strong>Exception: " & newUserID & "</strong><br />" & ex.ToString() & "<br /><strong>Message: </strong><br />" & ex.Message & "</p>" End Try End While usersLabel.Text &= "<p><strong>END OF RECORDS</strong></p>" ' Close the reader and the connection
reader.Close()
conn.Close() But now I want to do something in a Stored Procedure  USE [lnp]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[sp_createPortingDetailsWithUsers]-- Add the parameters for the stored procedure here @passUserId NVarChar(50), @passPassword NVarChar(50)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;Membership.CreateUser(@passUserId, @passPassword)Select SCOPE_IDENTITY()ENDBut when I try to execute this in SQL SERVER MANAGEMENT STUDIO EXPRESS, I get an error near the line MEMBERSHIP.CREATEUSER(@passUserId,@passPassword)Msg 102, Level 15, State 1, Procedure sp_createPortingDetailsWithUsers, Line 11Incorrect syntax near 'Membership'. What I want to do is create a user with a UserID and Password that I am passing from a table and then retrieve the USERID (unique identifier)  when I create the new user. Can anyone suggest or show a tutorial on what I am doing wrong? I appreciate any assistance in advance,Chuck 

View 4 Replies View Related

Retrieve Id Of Newly Added Row In Stored Procedure

Jan 5, 2006

Hi,

I am trying to return the id of the newly added row after the insert
statement in my stored procedure. I was told to "RETURN
SCOPE_IDENTITY()", which i did. The problem is i do not know how to get
this value in asp.net?

I added this code below in my business layer. myDAL refers to an object
of my DAL layer. In my DAL layer, i have a addPara() method which will
help me dynamically add as many parameters. Someone please advise me on
what to do. Thanks

myDAL.addPara("@RETURN_VALUE", , SqlDbType.Int, , ParameterDirection.ReturnValue)

Stored Procedure:
CREATE PROCEDURE [ADDPROMOTION]
(
@PROMOTIONNAME VARCHAR (100),
@PROMOSTARTDATE DATETIME,
@PROMOENDDATE DATETIME,
@DISCOUNTRATE INT,
@PROMODESC VARCHAR(100)

)

As
-- INSERT the new record
INSERT INTO
MSTRPROM(PROMOTIONNAME, DISCOUNTRATE, PROMOSTARTDATE,
PROMOENDDATE, PROMODESC)
VALUES
(@PROMOTIONNAME, @DISCOUNTRATE, @PROMOSTARTDATE, @PROMOENDDATE, @PROMODESC)

-- Now return the InventoryID of the newly inserted record
RETURN SCOPE_IDENTITY()
GO

View 3 Replies View Related

How To Retrieve Large String From Stored Procedure?

May 4, 2007

Hello! This is my scenario...

Development - Visual Studio 2005
Database - MS SQL 2005

I have written a stored procedure that has a output parameter whose type is NVARCHAR(MAX). Then I use C# to wrap this stored procedure in OLEDB way. That means the OleDbType for that parameter is VarWChar.

This works fine if the string size is less than 4000. If more than 4000, the remaining part will be chopped off. The situation gets worst if I replace VarWChar with LongVarWChar. There is no error nor warning at compiling time. But there is an exception at run time.

So... Does anyone here can help me out? Thanks in advance.

View 6 Replies View Related

Retrieve Multiple Variables From Stored Procedure (SQLHelper)

Jun 22, 2004

Hi all,

I am using SQLHelper to run a Stored Procedure.
The Stored Procedure returns 3 variables:

ie:

SELECT @Hits = COUNT(DISTINCT StatID) FROM Stats

...etc...

The SP is currently called as below:

SqlParameter[] sqlParams = new SqlParameter[]
{
new SqlParameter("@FromDate", Request["FromDate"]),
new SqlParameter("@ToDate", Request["ToDate"]),
};


My question is this: How do I retrieve these variables?

I know I need to declare a new SqlParameter and change it's Direction to Output but I am unsure of the exact syntax required to do this.

Thanks in advance,

Pete

View 5 Replies View Related

Retrieve Count From Stored Procedure And Display In Datagrid.

Feb 9, 2006

Hi Guys,
I have a sql procedure that returns the following result when I execute it in query builder:
CountE   ProjStatus
6            In Progress
3            Complete
4            On Hold
The stored procedure is as follow:
SELECT     COUNT(*) AS countE, ProjStatusFROM         PROJ_ProjectsGROUP BY ProjStatus
This is the result I want but when I try to output the result on my asp.net page I get the following error:
DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error:



Line 271: </asp:TemplateColumn>
Line 272: <asp:TemplateColumn>
Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
Line 274: </asp:TemplateColumn>
Line 275: </columns>
My asp.net page is as follows:
<script runat="server">
Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")

' Mark the Command as a SPROC
myCommandPS.CommandType = CommandType.StoredProcedure
Dim num as integer
num = CInt(myCommand.ExecuteScalar)
'Set the datagrid's datasource to the DataSet and databind
Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()

myConnection.Close()
</script>
 
<asp:datagrid id="dgProjSumm" runat="server"

BorderWidth="0"
Cellpadding="4"
Cellspacing="0"
Width="100%"
Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"
Font-Size="xx-small"
AutoGenerateColumns="false">

<columns>
<asp:TemplateColumn HeaderText="Project Summary" HeaderStyle-Font-Bold="true" >
<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem, "ProjStatus" ))%> </itemtemplate>
</asp:TemplateColumn>
<asp:TemplateColumn>
<itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
Please help if you can Im havin real trouble here.
Cheers
 

View 3 Replies View Related

Problem To Retrieve Data From SQL Server 7.0 Via Stored Procedure

Jun 16, 2001

I'm writing VB 6.0 codes to retrieve data from SQL Server 7.0. if the sql code is embedded in VB, everything works fine. But when I tried to use stored procedure, one error called "invalid object name 'author'" occurs. author is a table in my database and obviously is in the database. what's wrong?

appreciate for any suggestion!
...mike

View 1 Replies View Related

Stored Procedure To Retrieve Zipcodes Within A Specified Zipcode And Distance

Apr 28, 2008

Hi All,
Does anyone have a Stored Procedure that works perfectly to retrieve all zipcodes within a specified zipcode and distance radius - a zipcode and radius is passed and the Store Procedure result shows all zipcodes that falls within that range.

Thanks in advance

Ade

View 5 Replies View Related

Unable To Retrieve The @@Error Code In Stored Procedure

Aug 25, 2006

Hi All,

In my application on click of Delete button, actually I am not deleting the record. I am just updating the flag. But before updating the record I just want to know the dependency of the record ie. if the record I am deleting(internally updating) exists any where in the dependent tables, it should warn the user with message that it is available in the child table, so it can not be deleted. I want to check the dependency dynamically. For that I have written the following procedure. But it is updating in both cases.

CREATE proc SProc_DeleteRecord
@TableName varchar(50),
@DeleteCondition nVarchar(200)
As
Begin
Begin Transaction
Declare @DelString nvarchar(4000)
set @DelString = 'Delete from ' + @TableName + ' where ' + @DeleteCondition
execute sp_executeSql @DelString
RollBack Transaction --because i donot want to delete the record in any case
If @@Error <> 0
Begin
select '1'
End
Else
Begin
Declare @SQLString nvarchar(4000)
set @SQLString = 'Update ' + @TableName + ' set DeletedFlag=1 where ' + @DeleteCondition
execute sp_executeSql @SQLString
select '0'
End

End

Thanks & Regards

Bijay

View 4 Replies View Related

Retrieve An Output Parameter In Stored Procedure From Java Application

Jul 20, 2005

in my java application I've made a call to this stored procedureCREATE procedure pruebaICM@pANI varchar(20),@pTABLA varchar(20),@pInsert varchar(500),@pUpdate varchar(1000),@pFLAG varchar(1),@pResultado int OUTPUTasbeginDECLARE @ani varchar(20)declare @cliente intDECLARE @sentencia nvarchar(1000)DECLARE @tabla nvarchar(20)DECLARE @sentencia_where nvarchar(50)DECLARE @sql nvarchar(1050)SET NOCOUNT ONset @tabla = @pTABLAset @ani = @pANISELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N' whereani = ' + @aniexec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente OUTPUTSELECT @Clienteif (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')beginif (@cliente = 0)beginset @sentencia = N'insert into ' +@pTABLA + N' values (' + @pInsert + N')'EXEC sp_executesql @sentenciaset @pResultado = 1SELECT @pResultadoreturn @pResultadoendif (@cliente = 1)beginset @sentencia = N'update ' + @pTABLA +N' set ' + @pUpdateset @sentencia_where = N' where ANI =' + @pANIset @sql = @sentencia +@sentencia_whereEXEC sp_executesql @sqlset @pResultado = 2SELECT @pResultadoreturn @pResultadoendendelse if (@pFLAG = 'B') or (@pFLAG = 'Borrar')beginif (@cliente = 0)beginset @pResultado = 0SELECT @pResultadoreturn @pResultadoendelse if (@cliente = 1)beginset @sentencia = N'delete from '+@pTABLA + N' where ANI = ' + @pANIEXEC sp_executesql @sentenciaset @pResultado = 3SELECT @pResultadoreturn @pResultadoendendEXEC sp_cursorcloseendMy problem is that the ouutput param @pResultado haven't got any valueand don't return anything to the java application. How can I fix thisproblem?Thanka very much for helping me!!!!

View 2 Replies View Related

Looping Through Each Row In An XML Object Sent To A Stored Procedure

Jun 27, 2007

I have an XML object (sent as a string, received as an XML datatype) that's in a Stored Procedure. Each row in the XML file will have 1 value from it inserted into one of three tables. The tables are depended upon the other value from the XML file.The XML File is layed out as:<Values>    <value>       <value>1</value>        <key>My_Field</key>      </value>    <value>
       <value>3523.2</value>
        <key>My_other_Field</key> 
    </value></Values>I basically need to go through it row by row, find out what table I need to insert the value into using the key field.Any help with this would rock. I'm using SQL 2005. 

View 4 Replies View Related

How To Retrieve The Result From A Stored Procedure That Executes A Dynamically Built Up Sql Query?

Apr 2, 2008

We have a sort of complex user structure in the sense that depending on the type of user the data resides in different tables. Therefor I needed a stored procedure that finds out what table to look for a certain column in. Below is such a stored procedure and it works like it should but my problem is that I don't know how to retrieve the result (which should be a string so can't use RETURN).

I've tried using an OUTPUT variable but since I just run EXEC (@statement) in the end I can't really set an output variable the common way (as in EXEC @outputVariable = PMC_User_GetUserValue(arg1, arg2..)) or can I?

I have also tried to use SELECT to catch the result somehow but no luck and Google didn't help either so now I'm hoping for one of you... Notice that you don't have to bother about much of the code except for the end of it where I want it to return somehow or figure out a way to call this stored procedure and retrieve the result.

Thanks in advance
ripern

-- Retrieves the value of column @columnName for credential id @credID
ALTER PROCEDURE [dbo].[PMC_User_GetUserValue]
@credID int,
@columnName nvarchar(50)
AS

DECLARE @userDataTable nvarchar(50)
DECLARE @userDataID int
DECLARE @statement nvarchar(500)
SET @statement = ' '

SET @userDataID =
(SELECT PMC_UserMapping.fk_userDataID
FROM PMC_UserMapping
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)

SET @userDataTable =
(SELECT PMC_UserType.userDataTable
FROM PMC_UserType
INNER JOIN PMC_UserMapping ON PMC_UserType.id = PMC_UserMapping.fk_usertype_id
INNER JOIN PMC_User ON PMC_UserMapping.fk_user_id = PMC_User.id
WHERE PMC_User.fk_credentials_id = @credID)

SET @statement = 'SELECT ' + @columnName + ' AS columnValue FROM ' + @userDataTable + ' WHERE id=' + convert(nvarchar, @userDataID)

-- Checks whether the given column name exists in the user data table for the given credential id.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=@userDataTable AND COLUMN_NAME=@columnName )
BEGIN
EXEC (@statement)
END

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

Please Help Stored Procedure, Invalid Object #tempactions

Sep 24, 2004

Can you please correct my stored procedure, all i am doing is trying to create a temporary table #tempactions.
When i am doing the following on query analyzer: execute createtempactions
I am getting the error : Invalid object #tempactions


CREATE PROCEDURE dbo.Createtempactions
AS
DECLARE @SQL nvarchar(2000)

if exists (select * from #tempactions)
Begin
Set @SQL = 'drop table #tempactions'
Exec SP_ExecuteSql @SQL
End

Set @SQL = 'Create Table #tempactions(Actionno INT NOT NULL,'+
'actioncode VARCHAR(200),'+
'assignedto VARCHAR(200),'+
'company VARCHAR(200),'+
'duedate datetime,'+
'completedate datetime,'+
'actiondescription VARCHAR(200),'+
'status VARCHAR(200),'+
'comment VARCHAR(200))'
Exec SP_ExecuteSql @SQL
GO


Thank you very much.

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

How To Call Remote Object Using C# Stored Procedure

Nov 29, 2005

I want to call a windows based service running with remote objects listner from a C# Stored procedure. Any idea how to do that?

View 2 Replies View Related

Stored Procedure Creation,Invalid Object Name

May 25, 2008



I am trying to create a new stored procedure in Sql server managment studio express in a database.
I am getting an error message saying

Invalid object name 'Consumer_delete'.

Can you please tell why I am getting this error message?? Also , I need to make sure that the created procedure appears in the list of database objects after execution.
Thanks for your help



SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE Consumer_delete

@ConsumerID int,

@BusinessId int

AS

BEGIN

DECLARE @intError int

DECLARE @ConsBusinessID int

SET NOCOUNT ON

SELECT @ConsBusinessID = CONSUMERBUSINESS.[ID]

FROM CONSUMERBUSINESS

WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId

DELETE FROM CONSUMERBUSINESS

WHERE ConsumerID = @ConsumerID and BusinessId = @BusinessId



DELETE FROM CUSTOMERREMINDER

WHERE ConsumerBusinessID = @ConsBusinessID

DELETE FROM NOTE

WHERE ConsumerBusinessID = @ConsBusinessID

DELETE FROM VISIT

WHERE ConsumerBusinessID = @ConsBusinessID



--ERROR HANDLING--------

SET @intError = @@ERROR

IF @intError <> 0

GOTO ExitError

RETURN 0

ExitError:

RETURN @intError

END

View 3 Replies View Related

Java Code To Retrieve Data From Stored Procedure Which Returns Cursor Varying Output?

May 11, 2015

java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.

View 3 Replies View Related

How Would You Execute Sp_changeobjectowner Stored Procedure Using Sqldmo Object

Jul 20, 2005

How do you use sqldmo object to execute system stored procedures? Anysuggestions would be appreciated.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Stored Procedure Won't List In 'Object Explorer' Window

Mar 25, 2008

I saved a stored procedure (see below) and I'm not seeing listed within the 'Databases' / 'Remote_Serials' (DB name) / 'Programmability' / 'Stored Procedures' folder within the 'Object Explorer' window.

I'm trying to perform a simple add-info-from-form-into-DB process.

Is it because of the way I put together the procedure? I have a feeling that it is (was going off a video demo, which created it within VB.NET 2008 Express, but I'm not able to connect to the DB from there; it says the file is open, when I completely closed out of SQL Management Studio Express).



Code Snippet
ALTER PROCEDURE dbo.spInsertSerialInfo
@EmployeeID as nchar(10),
@First_Name as nvarchar(50),
@Last_Name as nvarchar(50),
@HAddress as nvarchar(50),
@City as nvarchar(30),
@HState as nvarchar(2),
@Zip as nvarchar(10),
@Phone_Home as nchar(15),
@Phone_Cell as nchar(15),
@Monitor1 as nvarchar(50),
@Monitor2 as nvarchar(50),
@PIX_ASA_Box as nvarchar(50),
@System_Case as nvarchar(50),
@Batt_APC as nvarchar(50),
@current_count as int OUTPUT

AS

INSERT RemSerials (EmployeeID,
First_Name,
Last_Name,
HAddress,
City,
HState,
Zip,
Phone_Home,
Phone_Cell,
Monitor1,
Monitor2,
PIX_ASA_Box,
System_Case,
Batt_APC)

VALUES (@EmployeeID,
@First_Name,
@Last_Name,
@HAddress,
@City,
@HState,
@Zip,
@Phone_Home,
@Phone_Cell,
@Monitor1,
@Monitor2,
@PIX_ASA_Box,
@System_Case,
@Batt_APC)

SELECT @current_count = Count(*) From RemSerials






When trying to access the stored procedure from VB.NET 2008 Express, it says, "Could not find stored procedure 'spInsertSerialInfo'."

I'm listing the code as below.



Code Snippet
cmd.CommandText = "spInsertSerialInfo"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con


cmd.Parameters.Add("@EmployeeID", SqlDbType.NChar, 10).Value = txtEmployeeID.Text





If I change the 'CommandType.StoreProcedure' to 'CommandType.Text' it seems to find it, but then I get another error saying that the @EmployeeID variable has already been delcared and that I need to have a unique variable.

Any help would be greatly apreciated

View 1 Replies View Related

How Do I Call A Insert Stored Procedure From A Data Flow Destination Object?

Jun 26, 2007

I want to insert data calling a stored procedure and call this from a Data Flow destination object. Is it possible?



I understand that Ole Db Command transformation object can call stored procedure, but that will not rollback in the event of error in the middle.



I understand that Ole Db Destination object will rollback in middle of import, but I don't see how to do the insert by calling stored procedure. "Sql Command" option in Ole Db Destination object does not seem to present solution to the problem.



Am I missing something here or is Ssis / Microsoft demanding that Insert stored procedure not be used when using Data Flow destination object to insert data into target table?

View 8 Replies View Related

SQL Server 2014 :: Execute Stored Procedure To Update A Table / Invalid Object Name

Jan 21, 2015

I am trying to execute a stored procedure to update a table and I am getting Invalid Object Name. I am create a cte named Darin_Import_With_Key and I am trying to update table [dbo].[Darin_Address_File]. If I remove one of the update statements it works fine it just doesn't like trying to execute both. The message I am getting is Msg 208, Level 16, State 1, Line 58 Invalid object name 'Darin_Import_With_Key'.

BEGIN
SET NOCOUNT ON;
WITH Darin_Import_With_Key
AS
(
SELECT [pra_id]
,[pra_ClientPracID]

[code]....

View 2 Replies View Related

Retrieve Data From A SQLDatasource Object.

May 21, 2007

I'm an "old" programmer but new to ASP.NET.
I want to get a value from the SQL Dataset.
What I would normally do in other environments is iterate through the dataset to get the value I would be intrested in, but I can't figure out how to do this without using a visual data display object like a Grid view.
Typically I want to get a value from the database that I then after manipulating it, like multply by 5, use to format something on the page.
thanks in advance,
Thommie
 

View 3 Replies View Related

Object Explorer Connection Fails - Failed To Retrieve Data.......SmoEnum)

Oct 17, 2006

Hi

First up - my apologies if this is not the right forum for this question. Please let me know if there is a more appropriate one for this question.

I have come across an issue with my SQL Server 2005 Express Edition install. It has been running fine since the install (a few months back). And I can still interact (query, update etc) the databases just fine. However, SQL Management Server Studio Express has started to produce an error message when I attempt to connect the Object Explorer.

The error message is shown below.

I have tried to access the link shown in the error message but there is no information provided there.

I can use SQL Management Server Studio Express to run queries on the databases so it would appear that the underlying services are all running OK.

The SQL Server is installed using SQL Authentication only and I have not made any changes to Service accounts or the services themselves. The protocols all appear to be in order too - Shared Memory, Named Pipes, TCP/IP are all enabled.

I have restarted the SQL services and even rebooted the machine, but to no avail.

I would really appreciate if anyone out there could give me some guidance on what may be causing this issue.

Note: The SQL Server is installed on WinXP Pro SP2 running in a VirtualPC. All apps are release versions (no beta or CTP)





TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

A severe error occurred on the current command. The results, if any, should be discarded.
A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

View 10 Replies View Related

Data Access :: MS Access ADODB Connection To Stored Procedure - Cannot Retrieve Data

Sep 22, 2015

I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend.  As a result, I'm going to write a number of Stored Procedures to replace the MS Access code.  My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-

USE [VMSProd]
GO
/****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

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

Procedure To Retrieve Primary Key

Nov 21, 2006

Hello!

I want to write a stored procedure, that will insert new row into a table and return the key of newly inserted row.

View 2 Replies View Related

Retrieve Store Procedure Name

Apr 11, 2008



Is possible to get the name of the store procedure that i'm executing ?

I need to get the name of the procedure inside the procedure being executed, to insert this value on a log file...

View 1 Replies View Related







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