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.
ADVERTISEMENT
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!
Where Does Indexes Stored In The SQL Server System Tables
Nov 19, 1998hi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL') and sysstat & 0xf = 3)
drop table dbo.MRDD_FINAL
This code was generated when I used the create a script to build a table from an existing table.
is there a way to check if a a table contains data or not,
The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing...
regards
Ali
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........
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
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
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 View RelatedPassing 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 RelatedDetermining What Are System Objects In Sp_help Or System Tables
Jul 20, 2005Hi,I have a few things on my databases which seem to be neither true systemobjects or user objects - notably a table called 'dtproperties' (createdby Enterprise manager as I understand, relating to relationship graphingor something) and some stored procs begining with "dt_" (some kind ofsource control stuff, possible visual studio related). These show up whenI use"exec sp_help 'databaseName'"but not in Ent. Mgr. or in Query Analyzer's object browser, and also notin a third party tool I use called AdeptSQL. I am wondering how thosetools know to differentiate between these types of quasi-system objects,and my real user data. (This is for the purpose of a customized schemagenerator I am writing). I'd prefer to determine this info with systemstored procs (ie sp_help, sp_helptex, sp_...etc) but will dip into thesystem tables if needed.Thanks,Dave
View 1 Replies View RelatedInserting Into Tables Using Stored Procedure
Dec 7, 2007I am currently building an electricity quoting facility on my website. I am trying to insert the data which the user enters into 3 linked tables within my database. My stored procedure therefore, includes 3 inserts, and uses the @@Identity, to retrieve the primary keys from the first 2 inserts and put it as a foreign key into the other table.
When the user comes to the quoting page, they enter their contact details which goes into a client_details table, then they enter the supply details for their electric meter these get inserted into the meter table which is linked to client_details. The supply details which the users enters are used to calculate a price. The calculated price, then gets put into a quote table which is linked to the meter table. This all seems to work fine with my stored procedure.
However I want to be able to allow a user to enter more than one meter supply details and insert this into the meter table, with the same client_id for the foreign key. This will also generate another quote to insert into the quoting table. However I do not know how to get this to work.
Should I be looking at using Sessions and putting a SessionParameter on the client_id for the inserts for these additional meters??
How To Search In 2 Tables By Using SQL Stored Procedure??
Mar 7, 2008Hello,
Is it possible to search in two tables, let's say table # 1 in specific field e.g. (age). If that field is empty then retrieve the data from table #1, if not retrieve the data from table # 2.
Is it possible to do it by using SQL Stored Procedure??
Thank you
Stored Procedure Checking 2 Tables
May 12, 2004ok, ill lay it out like this, and try not to be too confusing.
I need to check my trasaction table to see that the user with a UserID bought, keeping track(adding up) of how many times they bought the Product with a productID, , then i need to selcect items from product table with the productID for the items the user bought,
i know how to return the product information, and how to check the tranaction table and i think how to add up the amount of tranastions for that item
so bacially i need to select data from a product table, for the products that the user bought tahts stored in the transaction table.
well i tried not to be confusing, but i think i failed
Using Temporary Tables Within The Stored Procedure
Nov 15, 2006Hi,If one uses a temporary table/ table variable within a storedprocedure, will it use the compiled plan each time the stored procedureis executed or will it recompile for each execution?Thanks in advance,Thyagu
View 1 Replies View RelatedTemp Tables In Stored Procedure.
Oct 22, 2007Hi
I have one stored procedure which uses temp tables in it .
I am trying to use this stored procedure in my SSRS report as the source.
I am getting an error which says that the temporary table doesnot exist while validating the sql syntax in report.
Also, this stored proc executes in SSMS ithout any problem and giving the result as desired.
Can we use Temp tables in Stored proc and call that in SSRS?
If not, what is the workaround for that.
Thanks
Creation Of Two Tables In A Stored Procedure
Oct 17, 2006Hi,
I was wondering if there was a way to create two temp tables within the same stored procedure.
Can we have two create statements one after the other?
Thanks
Joining Two Tables In Stored Procedure
Apr 28, 2008Hi,
I want write STORED PROCEDURE to join TWO tables.
I am displaying all the details here. Please help me.
INPUT DATA:
Table1
(OLD DATA)
SYMBOL CATEGORY SHARES Prev.Close Last Trade
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma
211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746
1181.25
799.00
115.25
843.00
1850.70
382.60
256.20
226.60
676.00
613.35
1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55
Table2
(NEW DATA)
SYMBOL LAST TRADE
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20
OUTPUT DATA:
(OLD DATA) (NEW DATA)
as
SYMBOL CATEGORY SHARES Prev.Close Last Trade
ABB
ACC
AMBUJACEM
BHARTIARTL
BHEL
BPCL
CAIRN
CIPLA
DLF
DRREDDY
electrical
cement
cement
telecom
electrical
refinery
petrochemical
pharma
construction
pharma
211908375
187634983
1522375422
1897907446
489520000
361542124
1778399420
777291357
1704832680
168172746
1170.15
782.40
115.45
922.40
1870.00
392.90
255.80
224.20
667.85
616.55
1153.25
766.95
113.45
928.05
1866.30
390.90
258.70
214.55
668.50
625.20
Inserting Simultaneous Tables Using Stored Procedure
Feb 8, 2007I am trying to insert into two tables simultaneously from a formview. I read a few posts regarding this, and that is how I've gotten this far. But VWD 2005 won't let me save the following stored procedure. The error I get says “Incorrect syntax near ‘@WIP’. Must declare the scalar variable “@ECReason� and “@WIP�.� I'm probably doing something stupid, but hopefully someone else will be able to save me the days of frustration in finding it. Thanks, the tables and procedures are below.
I made up the two tables just for testing, they are:
tbltest
ECID – int (PK)
Linking Tables In Data Set Or Stored Procedure
Mar 10, 2008Im currently in the process of developing a new system in ASP.net. The system uses multiple tables from a SQL database. To link information from say the products table to the suppliers table a unique key from the suppliers table is stored in the products table to show which products each supplier has bought...simple so far.
From this I can then pull out any of the information relating to that product instead of just displaying the ID. Currently this is done by creating a stored procedure and then dragging the stored procedure onto the dataset layer to create the data adapter.
Im not 100% this is the best way to be doing this. Is it possible to simply just link the tables in the dataset layer to display a string from another table instead of an ID referencing number?!
any help on this would be much appreciated as I cant really continue untill I have sorted out the data structure problems.Thanks in advancedmowfo
Insert Stored Procedure For Related Tables
Feb 24, 2005I have two sets of related tables: Quote - QuoteDetail and Order - OrderItem
I need to copy Quote - QuoteDetail records to Order - OrderItem tables
I have the stored procedure up to this point: Insert a Quote in the Order table and get the new Order @@Identity.
I need to insert the QuoteDetail records into the OrderItem table using the new OrderID
Thank you for your help.
Querying Temporary Tables From A Stored Procedure
Jun 7, 2005What I am trying to do now is combine multiple complex queries into one table
and query it showing the results on an ASP.net page.
I am currently using SQL Server 2000 backend. Each one of these queries are
pretty complex so I created each query as a Stored Procedure. These queries are
dynamic by each user, so the results will never be the same globally.
What I have done so far was created a master stored procedure passing the
current user's username as a parameter. Within the master SP (Stored Procedure)
it creates a temporary table inserts and executes multiple stored procedures and
inserts into the temporary directory. Each of the sub stored procedures all
have the same columns. After the insert to the temp tables, I then query the
temp table and return it to the function it was executed in code and fill it as
a System.Data.DataTable. I then bind the DataTable to a
Repeater.DataSource.
Problem: When the page is rendered, it returns nothing. I
tested the master SP in the data environment and it works fine.
Suspect: ASP.net when the SP is executed, it sees that the
data is a disconnected datasource? Perhaps the session in the SQL Server when
the temp table is created isn't in SYSOBJECTS system table?
Here is an example of the code from the master SP:
CREATE PROCEDURE dbo.TM_getAlerts @Username
varchar(32)ASCREATE TABLE #MyAlerts ([DATE] datetime, [TEXT]
varchar(200), [LINK] varchar(200) )
INSERT INTO #MyAlertsEXEC
TM_getAlertsNewTasks @Username
INSERT INTO #MyAlertsEXEC
TM_getAlertsLateTasks @Username
SELECT [DATE] AS 'DATE', [TEXT]
AS 'TEXT', [LINK] AS LINK FROM #MyAlerts
GO
It is a fairly simple call... but why doesn't ASP.net doesn't see it when the
the DataTable is filled. I tried just executing one of the sub SP without
creating temporary tables and it works flawlessly. But the query in one of the
sub SP is a normal but complex select.
How To Get Data From All Related Tables Using Stored Procedure
Sep 16, 2005I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.
View 1 Replies View RelatedBind To Multiple Tables From Stored Procedure
Dec 4, 2005I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?
View 2 Replies View RelatedStored Procedure Referencing Moved Tables
Mar 12, 2001Hi everyone
I am totally confused, please help me. I am new to this
I am trying to split one DB (A) into two databases original (A) and new-Blank(B) by moving some tables from DB (A) to the new DB (B) however some of the tables has FK and Stored procedures referencing other tables that need to stay in DB (B), The questions are
1. after scritping these tables while they are in DB (A), and runing the script in DB (B) to re-create them, do I delete these table from DB (A), and the FK that references them.
2. What shall I do with the stored procedures. Turn them into trigers or else if turn them into trigers, in which DB should the triggers run (DB A of DB B),if these are becoming triggers do I delete the stored procedures from DB (A)
Any reply is appreciated