How can I create a dbo/system stored procedure, not dbo/user SP.
One more thing, if I created a new database TimeDB, I see nothing in stored procedure folder, how can see something (system procedures) as I knew that in SQL 97, we can see something (system procedures).
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
Greetings:
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?
Thanks!
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"?
Thanks!
Can I create system stored procedure in sql 7.0?
After I used 'alter' to modify a system sp, it's category
change from 'system' to 'user'. Is there way to change it back?
Thanks a lot!
Xiao
I am attempting to develop a stored proc that will do the following:
1) Take as an input parameter the filepath of a local directory
2) Return a recordset showing all files contained in the local directory
At the code level, I am attempting to do the following:
1) use system.io class to iterate through each file of a given local directory
2) parse out a union query to show all file names and their system creation times
3) insert the parsed sql into a sqldatareader, and send that out via a sqlpipe to the caller.
After compiling and deploying the CLR stored proc, I get the following when I try to execute:
Msg 6522, Level 16, State 1, Procedure spclr_wcl_get_file_info, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'spclr_wcl_get_file_info':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)
at System.IO.Directory.GetFiles(String path)
at StoredProcedures.spclr_wcl_get_file_info(String str_dir)
This occurs even though I've set the SQL Server service to run as a local administrator on the machine (ie, the account should have full rights to virtually any local directory or file).
I found the following article which I thought might resolve:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494009&SiteID=1
but when I include the WindowsImpersonationContext, etc., I instead get the following:
Msg 10312, Level 16, State 49, Procedure spclr_wcl_get_file_info, Line 0
.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.
Was hoping someone could illuminate as to what I'm doing wrong? Below is a code sample. TIA.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Diagnostics;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void spclr_wcl_get_file_info(string str_dir)
{
// this clr stored proc will read all files in the trans log directory and return
// a recordset with file names and create dates.
//impersonate the calling user
System.Security.Principal.WindowsImpersonationContext newContext;
newContext = SqlContext.WindowsIdentity.Impersonate();
//enumerate files in directory, use to parse out union select query, return table with file names and create times.
string str_sql = "";
System.DateTime d_date;
FileInfo obj_fsi;
string[] str_arr_files = Directory.GetFiles(str_dir);
foreach(string str_file in str_arr_files)
{
obj_fsi = new FileInfo(str_dir + str_file);
if (obj_fsi.Exists)
{
d_date = obj_fsi.CreationTime;
str_sql += "SELECT CHAR(39)" + str_file + "CHAR(39) AS [file_name], ";
str_sql += d_date.ToLongDateString() + " file_creation_time UNION ";
}
}
// parse off the last 'union'
if (str_sql.Length > ("UNION ").Length)
str_sql = str_sql.Substring(0, str_sql.Length - ("UNION ").Length);
// use sql to send dataset back to caller.
SqlCommand obj_cmd = new SqlCommand();
obj_cmd.Connection = new SqlConnection("Context connection=true");
obj_cmd.Connection.Open();
obj_cmd.CommandText = str_sql;
SqlDataReader obj_reader = obj_cmd.ExecuteReader();
SqlPipe obj_pipe = SqlContext.Pipe;
obj_pipe.Send(obj_reader);
//clean up
obj_reader.Close();
obj_cmd.Connection.Close();
}
};
Hi,
I'm using the sp_start_job system stored procedure to run a job on the SQL Server Agent. The sp_start_job stored procedure takes a parameter called, step_name where you can begin execution from that step. My problem is that I only want to run that one step of the job rather than that step and every step after it. Is there a way to only execute one particular step? Or a safe way to successfully terminate the process after that step has finished. Any input would be much appreciated. Thanks.
I am using a web application using asp.net 1.1 vs 2003.
i am using sql server authentication where users are using their sql server user id and sql server saved passwords.
how can i change their passwords inside the sql server?
is there a system sp that allows one to pass one's user id and then change password.
I will need to call that sp from a user defined store dprocedure and pass the parameters and that will change the password on the sql server.
thanks
Is it possible to have single stored procedure which can return Weekly, Monthly, Quaterly and Yearly Report.
User input for Queries:
Weekly : Start Date & End Date
Monthly: Month & Year (Eg. Jan 2003, May 2004)
Quarterly: Quarter & Year (First Quarter 2003, ThirdQuarter 2004)
Yearly: Year
Currently I have 4 different Stored Procedure 1 for each reporting system.
I have seen that one can use CASE in Stored procedure.....
Pls give suggestion !!!
Thx to all who helped me for Stored Procedure previously
Here is what I have:
3 Drop Down Boxes:
1) List of property
2) Ticket Status
3) Tech Name
All 3 Drop boxes have default value of "All"
So, if all 3 drop boxes are "All" ie.
list of property = All
Ticket status = All
Tech Name = All
Query pulls up all records from database and displays it.
Lets say if I select Tech Name is XYZ then query should pull out all property, all ticket status by Tech XYZ.
Now my previous developer has if else case and he has total 9 query for doing all this. He has used SQL along with C# code.
I am trying to modify this if-else and convert it into Stored Procedure. Is there a way I can handle all with 1 stored procedure ?
Previous Reporting works like charm........
Hi,
Is it possible to store the output of a SQL Server 7.0/6.5 System Stored Procedure (eg. xp_fixeddrives, sp_spaceused, etc.) in a table, possibly with a Select Into?
All help will be greatly appreciated.
Thanx in advance.
Craig
I want to have a Stored procedure call another stored procedure gathering the info from some of the information SP#1 and use it calling SP#2...
For example sp_Help returns columns when executed (Name, Owner, Object_type).
So how can I reference the Name column and pass it to SP#2
Exec sp_Help
...?
SP#2 name=sp_help.Name ???
Thanks
Richard
Hello
I wonder if I can generate the "CREATE TABLE" sentence given the name of a table by means of a stored procedure.
Thanks a lot.
Is it possible to access the procedure which scripts a table to new query? My goal is to automate the creation of some tables.
View 4 Replies View Related
How do I pass system variables to a stored procedure? Is it possible to have an OLE DB transformation with the following sql command: exec InsertIntoLog @MachineName, @TaskName...? Do I have to use a Derived Transformation first to 'convert' variables into columns and then use exec InsertIntoLog ?, ? ...
Thanks for the help.
I built a database by using a generated script from the originaldatabase. It built the System Store Procedures as User type. How do Ichange them back to System type?
View 4 Replies View RelatedI am trying to catch the @retval which is returned finally after executing sp_update_schedule stored procedure (o or 1) but i cannot catch the final resultIf i put Print statement just before the return (@retval), then i am seeing 0 as output but i want to catch that value when i execute the SP with the parameters. How can i do that?? same thing with all other system stored procedures.thanks alot in advance....if you want more info on this Q, plz let me know
View 5 Replies View RelatedJust wonder whether is there any indicator or system parameters that can indicate whether stored procedure A is executed inside query analyzer or executed inside application itself so that if execution is done inside query analyzer then i can block it from being executed/retrieve sensitive data from it?
What i'm want to do is to block someone executing stored procedure using query analyzer and retrieve its sensitive results.
Stored procedure A has been granted execution for public user but inside application, it will prompt access denied message if particular user has no rights to use system although knew public user name and password. Because there is second layer of user validation inside system application.
However inside query analyzer, there is no way control execution of stored procedure A it as user knew the public user name and password.
Looking forward for replies from expert here. Thanks in advance.
Note: Hope my explaination here clearly describe my current problems.
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,
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.
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 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
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
Hi All,
Please tell me what this procedure dt_getobjwithprop_u does.
Thanks in advanced,
TN
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
Is thier any stored procedure to get system configuration
are some give me guide line or stored procedure to get system configuration
Please help me to get the xml from procedure in asp 64 bit system.
The below code is executed in 32-bit system and display the XML in browser. But if you run the same code in 64-bit system (windows 2003 standard R2) the values replaced with question marks and it it displayed the following error.
I am using SQl server 2000 as a DataBase.
This is very urgent.
Blow is the code with file name test64bit.asp
<%
Dim str_XML
dim cn
set cn = server.createobject("adodb.connection")
cn.ConnectionString = "Provider=SQLOLEDB;User ID=sa;Password=Password;Initial Catalog=northwind;Data Source=SystemName;Network Library=DBMSSOCN;"
cn.Mode = adModeReadWrite
cn.Open
set rsXml = Server.CreateObject("Adodb.recordset")
strSql = "select * from Products for xml auto"
rsXml.open strSql ,cn
while not rsXml.EOF
response.write rsXml(0).value
str_XML = str_XML & rsXml(0)
rsXml.MoveNext
wend
rsXml.close
response.write "str_XML::" & str_XML & "<br>"
%>
Following is the error
?ProductID?ProductName?SupplierID?CategoryID?Quant ityPerUnit?UnitPrice?UnitsInStock?UnitsOnOrder?Reo rderLevel?Discontinued?Products?Al
Microsoft VBScript runtime error '800a000d'
Type mismatch
/ASP/test64bit.asp, line 21
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 RelatedI 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