Is there any way to know the list of objects using one of my procedure? I could get the list of objects that my procedure uses via EM. Actually the documentation and design is pretty poor here, and I am fairly new working with SQL Server.
The version I am working on is Microsoft SQL Server 2000 - 8.00.2187 (Intel X86) Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Hello
Is there any way to find information regarding deleted objects (stored procedure or views or functions) in sql server
T.I.A
How to SQL server save information about create/drop objects action. How can I get this? Example for, a lot of objects(sp) in my database has been dropped, how I know who was dropped them (user login & time)?
View 1 Replies View RelatedHi all,I'm trying to get information about a given Stored Procedure (SP) but am able (until now) to get to extract only the name and columns either using T-SQL or SQLDMO. I want to extract information like which Tables the SP accesses, and also which Fields. This however doesn't seem to be available through T-SQL nor SQLDMO.I haven't been able to find any information yet, and am thinking that my only way to work this out is to design my own parser, which I've done to some extent - it extracts the tables and fields of every query inside the SP, but it gets a bit messy when the SP gets complicated, even when using regular expressions.I'm sure SQL must know a way to find out about these objects, since it manages to compile SP - so where does it maintain or (even better) expose information about the tables and fields each SP uses?Any help or pointers would be very very helpful!Regards,Pieter
View 1 Replies View RelatedHi,
I have a database with two handred tables.I want to get a list that have table name and numbers of record in each table.
Would you please give me a idea if you know how to do this with query or build-in stored procedure.
Thanks a lot
Mark
Hi
I have a stored procedure in SQL server 2005. It works fine when I execute it from the Management Studio.But when executing it from ASP.NET code like this:
..... Of course more code is executed before this call .....int retVal = this.odbcCreateDataBaseCommand.ExecuteNonQuery();
retVal is -1. But -1 doesn't really tell me what the problem is?
Is there anyway to get extended error information so I can figure out whats going wrong?
(The stored procedure was working fine in SQL server 2000 before I upgraded to SQL server 2005. I use .NET Framework 1.1 and ODBC Sql Native Client to access the 2005 server.)
Regards
Tomas
While trying to execute a stored procedure I am getting error that 'SELECT permission denied on table .......' The DBA has given execute permission for the sp and still the same error. What needs to be done. When permissions are given through the SP it implies that the objects are given permissions ?
Putting in db_datareader group will give permission to read from all tables across all the databases in the server. We want that the user should be able to read data from only those tables called in the sp. Normally in SQL 2000 we used to give EXECUTE permissions to the sp only. This in turn would be enough for that user to get data while executing the sp.
Hi All, I am trying my hand at SQL CLR. I want to know one thing which I'm not able to get from anywhere. Suppose I've a C# class like Student. I create an object of this class and push its values into database. Instead of pushing individual property values to database I want to pass the Student object itself or maybe an array or list etc. Kindly help me if it is possible.
Regards
one of my SQL Developer member had one observation that, size of the parameter 'Parameter_XYZ' in certain stored procedure had changed from 25 to 255 during some production fixes, however suddenly its looks like that, someone has changed it back to 25 instead of 255.
DECLARE @Parameter_XYZ
varchar(25);
Can we figure out in which sprint/drop the stored procedure was changed and the Parameter_XYZ back to 25. Can any log recovery mechanism will get such details.
Can we get stored procedure text between different alteration.
Hi Frens,
Could anybody tell me how can I find all the database objects that are used in /referenced by/called by/dependent on a given stored procedure? In other words, I am looking for something like a stored procedure or a function that takes as input the name of a stored procedure and outputs all the names of the tables, functions, procedures, cursors and etc. database objects that are used in that procedure. Could you please give me suggestions or possible answers for this?
Thanks a lot for your time.
Regards,
-Ram.
I have a code that is working, and want to create a store procedure in order to put the information of the running code into a new table.
View 7 Replies View RelatedI am trying to use a Transfer SQL Server Objects Task to copy a portions of tables from one db to a second db.
the connection is using Windows Authentication.
I am logged in as the Administrator
That user has sysadmin rights on the Sql Server in Question.
DropObjectsFirst = True
But when I execute the task I get the follwing warning
[Transfer SQL Server Objects Task] Warning: Stored procedure "{0}" already exists at destination.
What am I missing?
Thanks,Kim
please help newbieI need to create a lot of objects the same type (let's say: schemas)I wish to use paramerized block in loop to do so.- how to put names of my objects to such control-flow?belss you for help
View 5 Replies View Relatedpassing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo"
[code]....
Hello,
I have some troubles with IBM WebSphere Application Server using MS SQL Server 2005 JDBC Driver. I always get the error e.g.
java.lang.SecurityException: class "com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData"'s signer information does not match signer information of other classes in the same package
I found this Feedback but it seems to be closed.
A temporary solution for me was to delete the meta-inf directory of the JAR-File, but that can't be the solution.
Can anyone help me with this problem?
Simon
I have a job that runs each night that deletes all records from a table that are older than two weeks old. Is there some way I can view how many records were deleted from the table each night. Is it written to a log somewhere? Thanks in advance!
View 1 Replies View RelatedHi all,
This is my first thread here.
I got an information about
SET NO_BROWSETABLE ON/OFF
in your forum.. The replier told that this is an undocumented option.
My question is how you guys find information on such undocumented things....
Thanks in advance.
Saint
Hello,
Can anyone clarify the nuances of How to deal with the change management environment in SQL Server.
Thanks.
I am trying to get job information and have a query set up. However, I would also like to get the owner information. However, the job only stores the sid of the owner. Does anybody know how I could change this query to get the owner name instead of sid? Don't want to use the sp_help_job command.
SELECT @@SERVERNAME AS Server, msdb.dbo.sysjobs.job_id, msdb.dbo.sysjobs.name, msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description, msdb.dbo.sysjobs.notify_level_eventlog, msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend, msdb.dbo.sysjobs.notify_level_page, msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created, msdb.dbo.syscategories.name AS Category_Name, msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time, msdb.dbo.sysjobservers.last_run_outcome, msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date, msdb.dbo.sysjobservers.last_run_time, msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator, msdb.dbo.sysoperators.email_address, msdb.dbo.sysjobs.date_modified, GETDATE()
AS Package_run_date
FROM msdb.dbo.sysjobs INNER JOIN
msdb.dbo.syscategories ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id LEFT OUTER JOIN
msdb.dbo.sysoperators ON msdb.dbo.sysjobs.notify_email_operator_id = msdb.dbo.sysoperators.id LEFT OUTER JOIN
msdb.dbo.sysjobservers ON msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id LEFT OUTER JOIN
msdb.dbo.sysjobschedules ON msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
I have a report where the information is getting cut off the report.
At the beginning of or at the ending of the page it is dropping the information.
My layout setting are set 8.5in wide by 11in height. .5in for either left or right margin. 1in for top and bottom.
I run the report and I'm missing a record.
I adjust the height to 10in to force diffferent page breaks and it's there.
I have the setting in one of the groups set for a page break at end. If I uncheck that it doesn't help.Still no records.
What is going on?
I'm looking for books or information on command line configuration of settings for things like activating Remote Connections under the Service Area Configuration and setting a port for that connection.
What books would be good to get or where can I download papers on this?
Any help would be appreciated.
Hi!This is a very simple question and I'm sure you guys will help me a lot.I'm using Visual Basic 2005 for programming. I have one table on my MS SQL 2005 database that has an int column with a counter that needs to be incremented when a user registers.So when reading the value I use a simple SQL query like this:
SELECT counter FROM companies WHERE company=0
then I store the value in a local int variable and then I increment it. Then I update the incremented value.
UPDATE companies ...
I need every single customer to have an individual value. My question is how can I prevent an error, data corruption or whatever if two or more users want to register at the same time? I've been reading about lock update but I'm not sure how to implement it on Visual Basic 2005 and I don't want to store scripts on SQL Server.
I'll appreciate your comments and help on this situation.
Hi,
I have a problem here in my SQL Server Database. I want to find which trigger is working backend affecting my table input. Since the original author of this trigger was somebody else, I am not able to find the trigger information
A simple line of syntax would be great.
Thanks in advance
sandeep
I am doing an audit of user information in our database and was wondering if there is a way for me to see the last time a user logged into the database. I am using a SQL Server 7.0 box. Any Information would be greatly appreciated. Thanks!
View 1 Replies View RelatedHi,
In my database one user is aliased like a guest. When i run sp_helpuser on the particular database it is giving following result.
username login name database
-------- ----------- --------
aaaa NULL XYZ
NOrmally we fill see like this for GUEST User. Now i want to drop this user 'aaaa' only. IF i use
sp_dropuser 'null'
i think guest user will also drop from XYZ database.
So can anyone pls suggest me how to drop this user. I have to add a same user with full permissions. i know in 65 we have sysalternates table gives all alias information. Is there any table gives the same information in sql7.
Pls suggest me regarding this.
thank u
raj
Hi,
I administering SQL server and one of or Db (size ~7 Gb) have a table with size 396 MB and 25 indexes created on this one. We working with this DB using Siebel Call center.
I have met a very strange situation: when I tried to get some data (using Siebel) my request take ~5 min. I run Profiler and catched this T-SQL statement which Siebel send to SQL server. After that, I run this T-SQL query using Query Analyser and found that Query Optimiser used wrong index. (clustered index) and try to compare every 470436 rows from the tables.
This is the statement. @P1, @P2, and @P3 just variable which siebel transfer to SQL server.
SELECT ........
FROM
dbo.S_EVT_ACT T1
LEFT OUTER JOIN dbo.S_CONTACT T2 ON T1.TARGET_PER_ID = T2.ROW_ID
WHERE
((T1.APPT_REPT_FLG != @P1 OR T1.APPT_REPT_FLG IS NULL) AND
(T1.TEMPLATE_FLG != @P2 OR T1.TEMPLATE_FLG IS NULL)) AND
(T1.SRA_DEFECT_ID = @P3)
But I found the very strange situation - when I manualy put the value '1-FRK' instead of @P3 - Query Optimizer used RIGHT index and I had a very good performance.
And addition when I tried to run the first Query on the same table but with the only 1000 rows the Query Optimiser choose right index.
Wat's on, wat happend with My Query Optimizer? Wy his decision so different from time to time?
Thank's a lot for any information about how to make Optimiser obedient.
I need to write an auditing trigger that will capture time of an insert and user that is making the insert. The time part i think i can use getdate(), but how can I find out which user is making the insert? Any help appreciated.
View 2 Replies View RelatedHi,
Can we retrieve the Login Failed and succedded for SQL Server?
I want to know the login failed and succedded information of SQL Server.
Can we retrieve the same?
Let me know ASAP.
Thanks and Regards,
Pramod
Is there any way to use Transact SQL to get the detailed information on a process like you get when you use the GUI interface under the Enterprise manager for server activity.
Thanks!
Is there a way to view the triggers on a table using query analyzer?
I have a developer that wants to see this information without having to right click every table in the database and viewing the triggers through enterprise manager.
Thanks,
Brad
I just started at a new company. The security stinks, everyone logs into the servers as sa (including many applications). I am taking steps to tighten security and restrict access to the sa login. I would like to capture some additional information at logon as an interim measure.
Can anyone assist me in automatically recording the following:
Host Name / IP Address
Login time
I realize I could periodically run an sp_who2 and capture the results - I also realize that if I turn on auditing, the logon will be written to the SQL Srvr LOG - I'm looking for an elegant / low impact way to capture the logon and host information.
Thanks in advance for any help.
Scott Wright
I'm trying to access data from one table by matching ids pulled from another table. I can't quite get the syntax though if anybody could help I would appericate it.
Here is where I'm at
select * from table1 where variable 1 = (select distinct(variable 2) from table 2 where variable 3 = variable 4)
Hi,
I'm trying to get the information about the user who modified the record in the table. I'm able to capture the datetime of modification through trigger.
CREATE TRIGGER [upd_dtm] ON dbo.Sample
FOR INSERT, UPDATE
AS
BEGIN
UPDATE Sample SET update_dtm = (SELECT GETDATE())
WHERE Sample.ID IN (SELECT ID FROM inserted)
END
I'm stuck, how to get the User who modified it?
Any help is greately appreciated!