I want to access a key from appSettings section of web.config.
I have the number of days allowed for a user to activate his/her account as a key in appSettings.
I have a maintenance procedure to delete all accounts that are not activated before that many days.
In this context, i have to access web.config from stored procedure. The procedure will be scheduled as a JOB in sql server.
Thanks.
I have the following stored proceduredrop procedure ce_selectCity;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================create PROCEDURE ce_selectCity @recordCount int output -- Add the parameters for the stored procedure here --<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>AS declare @errNo int -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select ciId,name from ce_city order by name select @recordCount = @@ROWCOUNT select @errNo = @@ERROR if @errNo <> 0 GOTO HANDLE_ERROR return @errNoHANDLE_ERROR: Rollback transaction return @errNoGoand i was just testing it likeProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load db.connect() Dim reader As SqlDataReader Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection) Dim recordCountParam As New SqlParameter("@recordCount", SqlDbType.Int) Dim errNoParam As New SqlParameter("@errNo", SqlDbType.Int) recordCountParam.Direction = ParameterDirection.Output errNoParam.Direction = ParameterDirection.ReturnValue sqlCommand.Parameters.Add(recordCountParam) sqlCommand.Parameters.Add(errNoParam) reader = db.runStoredProcedureGetReader(sqlCommand) If (db.isError = False And reader.HasRows) Then Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />") While (reader.Read()) Response.Write(reader("ciId") & "::" & reader("name") & "<br />") End While End If db.close() End SubIt returns ALL ROWS (5 in the table right now). So, recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).BUT, its returning 0.What am i doing wrong??EDIT:Oh, and this is the function i use to execute stored procedure and get the readerPublic Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader sqlCommand.CommandType = CommandType.StoredProcedure Return sqlCommand.ExecuteReader End Function
View 5 Replies View RelatedHi All,
I have created a stored procedure (in SQL Server 2005 - Developer) with input and output parameters. Please somebody let me know how I can call this store procedure from code behind using TableAdapter i.e. through XSD.
Thanks,
Long Live Microsoft ;)
Hiya folks,
I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.
The syntax I'm using is as follows :
SELECT TOP 1 @MyJobN = IJobN FROM MyView
I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.
SELECT TOP 1 @MyJobN = IJobN FROM LimsLive.dbo.MyView
But alas to no avail!
Any offers kind people??
i'm trying to insert into db sql 2000 through a stored procedure .. i got this error " Procedure or function newuser has too many arguments specified "
this is the procedure :
ALTER PROCEDURE newuser
(@username_1 [nvarchar](80),
@email_2 [nvarchar](50),
@password_3 [nvarchar](256),
@Country_ID_4 [int],
@city_id_5 [nvarchar](10),
@gender_6 [nvarchar](50),
@age_7 [int],
@fname_8 [nvarchar](50),
@lname_9 [nvarchar](50),
@birthdate_10 [datetime])
AS INSERT INTO [Brazers].[dbo].[users]
( [username],
[email],
[password],
[Country.ID],
[city.id],
[gender],
[age],
[fname],
[lname],
[birthdate])
VALUES
( @username_1,
@email_2,
@password_3,
@Country_ID_4,
@city_id_5,
@gender_6,
@age_7,
@fname_8,
@lname_9,
@birthdate_10)
& that 's the code in asp page :
Dim param As New SqlClient.SqlParameter
SqlConnection1.Open()
param.ParameterName = "@username_1"
param.Value = TextBox1.Text
param.Direction = ParameterDirection.Input
SqlCommand1.Parameters.Add(param)
SqlCommand1.ExecuteNonQuery()
plz .. waiting any solve for this problem immediately
hi
i have stored procedure and i need to access another database in my stored procedure
I'm going to query a table which is located in another database
as you know it is impossible to use the USE database keyword in stored procedures
so what should I do?
thanks.
Hi,
Can anyone tell me how to access the results from a select statement within the stored procedure?
This is to implement auditting functionality, before updating a record i want select the original record and compare the contents of each field with the varibles that are passed in, if the fields contents has changed i want to write an audit entry.
So i'd like to store the results of the select statement in a variable and access it like a dataset. e.g
declare selectResult
set selectResult = Select field1,field2,field3 from table1
if selectResult.field1 <> @field1
begin
exec writeAudit @var1,@var2,var3
end
Many thanks.
I have a COM object that is written using Visual Basic 6. This is referenced in a .NET Stored Procedure. But when I execute the stored procedure I get an error:
Msg 6522, Level 16, State 1, Procedure prCalculator_ExecCalc, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'prCalculator_ExecCalc':
System.Runtime.InteropServices.COMException: Retrieving the COM class factory for component with CLSID {844E8165-ABC1-432B-9490-51B1A6D91E71} failed due to the following error: 80040154.
System.Runtime.InteropServices.COMException:
Here is what I do:
1. Compile the VB DLL.
2. Convert into a .NET assembly by importing to a Visual Studio 2005 project. Sign the interop assembly.
3. Register it as an assembly in SQL 2005 server.
4. Create a .NET stored procedure and reference the above assembly. Compile this assembly again in SQL 2005 and create a stored procedure using the assembly. This assembly is also signed.
Please Note:
1. All the assemblies are registered with UNSAFE permissions.
2. They are compiled with COM Visible flag in Visual Studio 2005.
3. This works perfectly on my local SQL Express where I have Visual Basic/VisualStudio 2005 installed.
4. I get this error, when trying on Test Server. I tried to install the VB Runtime on this machine and still does not work.
So, what am I missing? Thanks for your help.
.
Hi,
I am new to Sql Server 2005 Reporitng Services. I created a report in BI and used stored procedure as a dataset. When I run the report in preview mode it works fine and when I run it in report server/report manager, I am getting the following error:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for data set 'dsetBranch'. (rsErrorExecutingCommand)
Could not find stored procedure 'stpBranch'.
But I have this procedure in the db and it runs fine in the query analyzer and the query builder window in report project. When I refresh the page in Report manager, I am getting this error.
Input string was not in a correct format.
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.FormatException: Input string was not in a correct format.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2753715
System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) +102
Microsoft.Reporting.WebForms.ReportAreaPageOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +149
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +75
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
I have changed the dataset from procedure to a sql string and the report is working fine everywhere. But I have a business requirement that I need to use a stored procedure.
I am not sure why I am getting this error and I greatly appreciate any help.
Thanks
Hi ,
I have around 5 databases with same structure used to store data for different locations and services.
I have created an intermediate database which will have all the stored procedures to access data from above 5 databases.
My queries are common to all the databases. so,I would like to pass database name as an argument to my stored proc and execure query on specified database.
I tried this doing using "USE Databasename " Command. but it says ...We can not use "USE command " in stored proc or triggers. so ..what i did is ..
--------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE TestDB_access(@dbname varchar(100))
AS
BEGIN
DECLARE @localDB varchar(100)
Set @LocalDB=@dbname
if @LocalDB='XYZ'
EXEC ('USE XYZ ')
elseif @LocalDB='ABC'
EXEC ('USE ABC')
Select * from Mytable
END
---------------------------------------------------------------------------------------------------------------
When I run this from my database , it gives me an error "unable to find table "mytable".
What is the best way to make my queries work with all the databases.
Thanks in advance
Venu Yankarla
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 all,
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in
my SSMSE by using either of 2 sets of the following EXEC code:
(1)
USE AP
GO
--Code that passes the parameters by position
DECLARE @MyInvTotal money
EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%'
PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1)
GO
(2)
USE AP
GO
DECLARE @InvTotal as money
EXEC spInvTotal3
@InvTotal = @InvTotal OUTPUT,
@DateVar = '2006-06-01',
@VendorVar = '%'
SELECT @InvTotal
GO
////////////////////////////////////////////////////////////////////////////////////////////
Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class
/////////////////////////////////////////////////////////////////////
I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!??
I have 4 questions to ask for solving the problems in this project:
#1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1".
How can I do it?
#2 Question: Did I set the CommandType property of the command object to
CommandType.StoredProcedure correctly?
#3 Question: How can I define the 1 output parameter (@InvTotal) and
2 input parameters (@DateVar and @VendorVar), add them to
the Parameters Collection of the command object, and set their values
before I execute the command?
#4 Question: If I miss anything in print out the result for this project, what do I miss?
Please help and advise.
Thanks in advance,
Scott Chang
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?
Thanks for your time:
Using the SQLdatasource wizard, selecting "Specify a custom statement or Stored Procedure", but the "Stored Procedure" radio button and drop box are NOT enabled (greyed out) and cant be selected. Login has sa and no schema's are implemetned yet... Help?
Is there a setting I'm missing? (like how table needs a primary key set for wizard to allow auto-generate statements)
Otherwise, Is there a work-around I can use?
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
Hi,I was just wondering if it's possible to access views from stored procedures? I know it doesn't make much sense, but would it be possible? If so, can you also give me some code example?Thanks.
View 4 Replies View RelatedCan anyone tell me is it possible to register and access .NET dlls from Stored procedures
and how to call those methods in those dlls from Stored procedures
Hi
I have a sql task in my control flow, which is like
Resultset = Single Row, SQL SourceType = Direct Input, SQL Stmt = EXEC StoreProcA ?
Result Name VariableName
StartDate UserStartDate
EndDate User:EndDate
Above SQL task is working fine... My question is I have an another SQL task immediately below that one.SQL Stmt is
SELECT CreditDebitDiff
FROM (SELECT C.BalanceNbr - D.BalanceNbr As CreditDebitDiff
FROM dbo.BalanceLog C, dbo.BalanceLog D
JOIN Work.CycleControl cc
ON cc.CycleControlId = D.CycleControlId
WHERE C.BalanceItemId = '81041'
AND D.BalanceItemId = '81031'
AND cc.CycleDate = ?) AS D
Parameter0 is mapped to User:EndDate and ResultName mapped to User:CreditDebitDiff (Datatype: Double)
SQL Task fails. I know I am making a mistake but dunno how to correct it. I can do it with a store proc but don't want to.
Any help would be greatly appreciated...
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
I have several hosted websites using MS SQL Server 2005 and ASP.NET 2.0 set up as DotNetNuke portals. I need to do a minor patch in a stored procedure to fix buggy functionality in one module. I have the patch, but what do I need to install on my local machine in order to edit the stored procedures on the hosted database?
The hosting company's support said to install SQL Server Management Services (SSMSEE.msi), but the install fails giving a link to a pre-requisite component called ADOMD.msi. That one seems to install okay, but I still get the same error when trying to install the Management program.
Perhaps I need SQL Server Express on my local machine? I'm really just looking for the best way to access and edit stored procedures on my hosted database?
I would REALLY appreciate a nudge in the right direction...?
Thanks.
Michael E. Smith
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI 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
Hi
When images are uploaded and stored directly into a sql database as binary data (eg in the club starter kit) how can those images be accessed and displayed.
When I open the images table in VWD and select display data, the cells holding the image data hold a <binary data> tag. What I want to be able to do is get at that data, or actually get at the image so that it is displayed. My reason is this, at the moment the only way to access the images in the sql database after they have been uploaded is to log into the website and view them as an administrator of the site. It would be much simpler if I could access the database directly and view the contents of the images table.
Any ideas?
Thanks
Can I use sqlDatareader with a stored procedure to return more than one reultset that I ll be accessing through MyDatareader.MoveNext
Any Little example on both the stored proc and the .Net side
Thanks
Hello,
What permissions do I need to set on our new SQL Server 2005 test server so that I can see the stored procedures, views and tables from Visual Studio development environment.
Example, I can see my older SQL Server 7 tables, edit them, write stored procedures, and so on. But while I can see the SQL Server 2005 and its databases, the folder underneath are empty and I cannot right-click to create New stored procedure or table.
My guess is that there is security involved, if so, what do I set on the new server?
If I'm wrong, and it's something else entirely, please advise.
Thanks!
Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
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
Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
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
View 16 Replies View Related