System Stored Procedure To Script Table
Sep 30, 2014Is 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
ADVERTISEMENT
System Stored Procedure Output Into Table
Nov 29, 1999Hi,
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
System Stored Procedure Call From Within My Database Stored Procedure
Mar 28, 2007I 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
How To Save Stored Procedure To NON System Stored Procedures - Or My Database
May 13, 2008Greetings:
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!
Stored Procedure Being Saved In System Stored Procedures
Apr 7, 2006We 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!
System Stored Procedure
Feb 24, 2005How 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).
Creating System Stored Procedure In 7.0
Mar 14, 2001Can 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
Using System.IO In A SQL2005 CLR Stored Procedure
Mar 1, 2007I 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();
}
};
System Stored Procedure Question
Jun 27, 2007Hi,
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.
System Stored Procedure Name That Allows One To Change One's Password ?
Jan 2, 2008I 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
Stored Procedure For Reporting System Issue
Dec 17, 2003Is 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 !!!
Another Reporting System Stored Procedure Issue
Jan 2, 2004Thx 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........
How Can Your Reference Columns In A System Stored Procedure
Nov 5, 2002I 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
Is There A System Stored Procedure That Scripts Tables?
Apr 25, 2007Hello
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.
Passing System Variables To Stored Procedure
Sep 24, 2007
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.
How Do I Change The Type Of A Stored Procedure From User To System?
Aug 11, 2005I 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 RelatedSystem Stored Procedure Doesn't Return Result (eg: Sp_update_schedule)
Apr 9, 2007I 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 RelatedHow To Create A System Type Table/ Change User Table To System Table.
May 23, 2007Is there any Posibility to change a User Table to System Table.
How to create one system table.
I am in Big mess that One of the Table I am using is in System Type.
I cant Index the same. Is there any Mistake we can change a user table to system table.....
What Is The Difference Between: A Table Create Using Table Variable And Using # Temporary Table In Stored Procedure
Aug 29, 2007which is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View RelatedStored Procedure To Copy Table 1 To Table 2 Appending The Data To Table 2.
Jan 26, 2006Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.
View 2 Replies View RelatedTransact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?
Nov 5, 2015Can I invoke stored procedure stored inside from a user defined table column?
View 5 Replies View RelatedUsing Table Name Stored In A Scalar Variable In Stored Procedure Problem
Mar 27, 2006Hello to all!
I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:
For example:
declare @countRows int
set @countRows = (select count(*) from MyTable)
The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:
declare @countRows int
set @countRows = (select count(*) from @myTableName)
I also tried this:
declare @sqlQuery varchar(100)
set @sqlQuery = 'select count(*) from ' + @myTableName
set @countRows = exec(@sqlQuery)
But it looks like function exec() does not return any value...
Any idea how to solve this problem?
Thanx,
Ziga
Power Pivot :: Temp Table Or Table Variable In Query (not Stored Procedure)?
Jul 19, 2012I don't know if it's a local issue but I can't use temp table or table variable in a PP query (so not in a stored procedure).
Environment: W7 enterprise desktop 32 + Office 2012 32 + PowerPivot 2012 32
Simple example:
declare @tTable(col1 int)
insert into @tTable(col1) values (1)
select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function
Apr 18, 2007Here is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END
Error Inserting Image Into SQL Server2000 Table From Pocket PC Application Only When Using Stored Procedure In Table Adapter Wiz
Apr 24, 2008My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Is there any limitation in CF?
Regards,
Professor Corrie.
Stored Procedure That Fetch Each Row Of A Table And Update Rows In Another Table
Jan 31, 2006I am working with the following two tables:
Category(NewID,OldID)
Link(CategoryID,BusinessID)
All fields are of Integer Type.
I need to write a stored procedure in sql 2000 which works as follows:
Select all the NewID and OldID from the Category Table
(SELECT NewID,OldID FROM Category)
Then for each rows fetched from last query, execute a update query in the Link table.
For Example,
Let @NID be the NewID for each rows and @OID be the OldID for each rows.
Then the query for each row should be..
UPDATE Link SET CategoryID=@CID WHERE CategoryID=@OID
Please help me with the code.
Thanks,
anisysnet
Update Temp Table With Stored Procedure Joined With Table
Sep 8, 2006Hello
Is it possible to insert data into a temp table with data returned from a stored procedure joined with data from another table?
insert #MyTempTable
exec [dbo].[MyStoredProcedure] @Par1, @Par2, @Par3
JOIN dbo.OtherTable...
I'm missing something before the JOIN command. The temp table needs to know which fields need be updated.
I just can't figure it out
Many Thanks!
Worf
How To Stored A Stored Procedure's Results Into A Table
Jul 23, 2005Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedStored Procedure For Insert Data From One Table To Another Table
Apr 25, 2006Hi,
I am having 2 tables. One is main table and another is history table. Whenever I update the main table, I need to insert the all the main table data to History table, before updating the main table.
Overall it is like storing the history of the table updation.
How do i write a stored procedure for this?
Anybody has done this before?
Pls help me.
Stored Procedure Append All From Table A To Table B
Jan 29, 2013I've never used a stored procedure before - let alone created one. how to append records from table A to table B.
View 1 Replies View RelatedGetting The Table Name From Stored Procedure
Oct 11, 2007Hi,
I am having a SP which returns two Rowsets:
create proc GetSalesDetails as
select CustName, ProductPurchased from Customers where PurchaseDate > '10/10/2006'
select ProductName from Products where SalesDate > '10/10/2006'
Now in my code when I am filling the Dataset using this SP then it is giving the Table Names as "Table" and "Table1". Is there any way to get the actual table names respectively?
Cheers,
Soumen
Table Name In A Stored Procedure
Mar 9, 2004How can be used in a stored procedure the name of a table that was passed as a varchar parameter? The table name must be used in the "from" clause of a "select" statement without calling the "EXECUTE" or "sp_executesql". Is it possible?
Marius G.
Differentiate Between Whether Stored Procedure A Is Executed Inside Query Analyzer Or Executed Inside System Application Itself.
May 26, 2008Just 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.