Sp_sproc_columns Does Not Seem To Work On Managed Stored Procedures
Jul 10, 2007
Hi,
I have written a stored procedure using C#.NET. I deployed it to SQL Server, and everything works fine.
However, an Third-Party app needs to see what parameters it can use for this SP by executing sp_sproc_columns <SP name>
sp_sproc_columns
Seems to work on normal SP's written in T-SQL, but not on Managed SP's. On my SP, sp_sproc_columns does not seem to work. It does not return the parameters. On normal SP's, it does.
CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterDiscrimination('Page Aggregate By Month2','001','010',0.0005,true)
Internal error: An unexpected exception occured. Execution of the managed stored procedure GetClusterDiscrimination failed with the following error: Exception has been thrown by the target of an invocation.Object reference not set to an instance of an object..
Hello I have restored a SQL 2000 backup of my database, ever since my web applications are unable to use any of the stored procedures. I get the following error: Could not find stored procedure 'xxx'.
If I use enterprise manager and go to the procedures tab the procedure appears there.
Any ideas what is up?
PS: The username has been changed, before the backup it was "user1" now its something else. For some reason it still shows that the procedure object is owned by "user1". Could this be the problem?
In my SQL Server Management Studio Express (SSMSE), pubs Database has a Stored Procedure "byroyalty":
ALTER PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
And Table "titleauthor" is: au_id title_id au_ord royaltyper
172-32-1176 PS3333 1 100
213-46-8915 BU1032 2 40
213-46-8915 BU2075 1 100
238-95-7766 PC1035 1 100
267-41-2394 BU1111 2 40
267-41-2394 TC7777 2 30
274-80-9391 BU7832 1 100
409-56-7008 BU1032 1 60
427-17-2319 PC8888 1 50
472-27-2349 TC7777 3 30
486-29-1786 PC9999 1 100
486-29-1786 PS7777 1 100
648-92-1872 TC4203 1 100
672-71-3249 TC7777 1 40
712-45-1867 MC2222 1 100
722-51-5454 MC3021 1 75
724-80-9391 BU1111 1 60
724-80-9391 PS1372 2 25
756-30-7391 PS1372 1 75
807-91-6654 TC3218 1 100
846-92-7186 PC8888 2 50
899-46-2035 MC3021 2 25
899-46-2035 PS2091 2 50
998-72-3567 PS2091 1 50
998-72-3567 PS2106 1 100
NULL NULL NULL NULL //////////////////////////////////////////////////////////////////////////////////////////// I try to do an ADO.NET 2.0-VB 2005 programming in my VB 2005 Express to get @percentage printed out in the VB Form1. I read some articles in the websites and MSDN about this task and I am very confused about "How to Work with Output Parameters & Report their Values in VB Forms": (1) Do I need the Form.vb [Design] and specify its properties of the object and classes I want to printout? (2) After the SqlConnectionString and the connection.Open(), how can I bring the value of @percentage to the Form.vb? (3) The following is my imcomplete, crude draft code:
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=pubs;Integrated Security=SSPI;"
Dim connection As SqlConnection = New
SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("byroyalty", connection)
command.CommandType = CommandType.StoredProcedure ................................................................... .................................................................. etc. //////////////////////////////////////////////////////////////////////////////////////////////////////////////////// From the above-mentioned (1), (2) and (3), you can see how much I am lost/confused in attempting to do this task. Please help and give me some guidances and good key instructions for getting the output parameter printed out in the FORM.vb in my VB 2005 Express project.
When I try to deploy this managed stored proc assembly on SQL Server 2005 I get this error: Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x8007000e. You may fix the problem and try again later I looked on the net but found no documentation about it. I know the CLR v2.0 is working as I have some ASP.NET apps running from this server that use it. Any ideas?
I am developing a managed stored procedure in VS.NET 2005 and I am trying to add a reference to an user developed assembly (not a system one) but adding a reference to it is not possible as it doesnt let me import assemblies but rather reference the few limited ones in a list. Why is this??
I am trying to debug sql2000 sp from managed code app with VS.Net 2003 archetect Ed.. It did not stop at the break point within the sql sp. I did granted execute permission for sp_sdidebug. Do I need to attach any process?
Is there anything left off by the article? I referenced msdn article option 2: http://support.microsoft.com/default.aspx?kbid=316549
All -- Please help. I am using managed code in SQL Server 2005 and have a question about stored procedure name qualification. With a VS.NET 2005 Pro Database project, when I use >Build, >DeploySolution, it works great but my stored procedures are named with qualification using my domainusername.ProcName convention, something like this... XYZ_TECHKamoskiM.GetData ...when I want them to be named with the dbo.ProcName convention, something like this... dbo.GetData ...and I cannot see where this can be changed. Do you happen to know? Please advise. (BTW, I have tried going to this setting >Project, >Properties, >Database, >AssemblyOwner, and putting in the value "dbo" but that does not do what I want.) (BTW, as a workaround, I have simply scripted the procedures, so that I can drop them and then add them back with the right names-- but, that is a bit tedious and wasted effort, IMHO.) Thank you. -- Mark Kamoski
Partial Public Class StoredProcedures <Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Sub StoredProcedureTest(ByVal strAS400ServerName As String, _ ByVal strCompany As String, _ ByVal decSerial As Decimal, _ ByVal strSerialCode As String, _ ByVal strSerialScan As String, _ ByVal decMasterSerialNumber As Decimal, _ ByVal strCustomerPart As String, _ ByVal strTakataPart As String, _ ByVal strCustomerRanNo As String, _ ByVal strCustomerAbv As String, _ ByVal strDestinationAbv As String, _ ByVal decQty As Decimal, _ ByVal strCreatDate As String, _ ByVal decVoidSerialNo As Decimal, _ ByVal strProductionLineNo As String, _ ByVal strProcType As String)
Dim sp As SqlPipe = SqlContext.Pipe Dim strResult As Integer = 0 Dim strErrorText As String = String.Empty Dim dsData As New DataSet Dim parameter(15) As OleDbParameter If Not strAS400ServerName Is Nothing And strAS400ServerName <> String.Empty Then ' Populate parameter collection
If dsData.Tables.Count > 0 Then dsData.Tables(0).TableName = "Supreeth" Dim bitresult As String = dsData.Tables(0).Rows(0)(0).ToString() Dim errorstring As String = dsData.Tables(0).Rows(0)(1).ToString()
' I am not sure here SqlContext.Pipe.Send(bitresult) SqlContext.Pipe.Send("No errors")
End If
Else Throw New ArgumentException("AS400Db.GetAS400TraceabilityResult: AS400 server name is empty or invalid") End If
End Sub
Public Shared Sub RunDB2Sp(ByVal strProcedure As String, ByRef parms As OleDbParameter(), ByRef dsData As DataSet) '********************************************* ' Declare Variables '********************************************* Dim daAdaptor As OleDbDataAdapter Dim cmdAS400 As OleDbCommand 'Dim dstestMe As New DataSet Try cmdAS400 = CreateCommand(strProcedure, parms) daAdaptor = New OleDbDataAdapter(cmdAS400)
' Fill the Data Set daAdaptor.Fill(dsData) Catch expError As OleDbException daAdaptor = Nothing Finally daAdaptor = Nothing cmdAS400.Dispose() 'Me.Close()
End Try
End Sub Public Shared Function CreateParameter(ByVal name As String, _ ByVal type As OleDbType, _ ByVal size As Integer, _ ByVal direction As ParameterDirection, _ ByVal paramValue As Object) As OleDbParameter Dim param As OleDbParameter = New OleDbParameter param.ParameterName = name param.OleDbType = type param.Size = size param.Direction = direction param.Value = paramValue Return param End Function
Private Shared Function CreateCommand(ByVal strProcedure As String, ByVal prams As OleDbParameter()) As OleDbCommand Dim CmdSAS400 As OleDbCommand Dim parameter As OleDbParameter Dim connAS400 As OleDbConnection connAS400 = New OleDbConnection("Provider=IBMDA400;Data Source=AHISERIESDEV1;User Id=****;Password=****;") connAS400.Open()
CmdSAS400 = connAS400.CreateCommand() CmdSAS400.CommandText = strProcedure CmdSAS400.CommandType = CommandType.StoredProcedure CmdSAS400.Parameters.Clear() 'CmdAS400.CommandTimeout = intTimeOut If (prams Is Nothing) Then Else For Each parameter In prams CmdSAS400.Parameters.Add(parameter) Next
End If
Return CmdSAS400
End Function
I have a UI which supplies 16 parameters to my stored procedure , which in turn call another sored procedure on as400 which returns result set. So far i am able to send 16 parms and get the values in dataset. My question here how would i send the result set to UI for display, please feel free to comment on any changes need to be made on code . I badly need to find a solution for this and i appreciate any feed backs
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
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?
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
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 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
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd (@buildd nvarchar(4), @avdate as nvarchar(25)) Returns nvarchar(25) as Begin declare @ret nvarchar(25) declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from ( select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
My company uses some integration software to synchronize tables in our OpenVMS server to our MS SQL 2005 server so statistics and tasks can be performed there instead potentially impacting our main data processing operations.
The problem I'm running into is that timestamps in the tables are stored as date_shipment (datetime) and time_shipment (integer) instead of as a combined datetime_shipment (datetime). Date_shipment contains the date of the transaction with 00:00:00.000 for the time part, while time_shipment has the time of the event stored as an integer, so 8:00 am -> 800, 4:50 pm -> 1650, 8:00 pm -> 2000, etc.
Unfortunately, I'm stuck with what the integration software gives me, so I need to find some way of turning the time integer column into a real time that I can then combine with the date.
Does anyone know of a way to do this? I haven't had any luck while experimenting with different T-SQL yet.
