I'm actually taking Microsoft's 2779 and just finished a lab where we
kept track of our changes to the database.
However, I'm not happy with the scripts interface because it does not
tell me the chronological order of my changes to the database.
Could someone share with me their technique for keeping track of
database changes?
I'm actually thinking a set of tables would be best, because sometimes
you want to know what database object you made a change to and other
times you want to know when you did something...
I have a problem concerning keeping track of a value within a query. I have a table that tracks invoices recieved and payments made. For each invoice number there may be multiple payments made against it. I need something that will check and make sure that each invoice number has its payments equal to its received amount.
I have a trigger that keeps track of status changes...
IF UPDATE(STATUS) BEGIN DECLARE @currentdate datetime DECLARE @currentstatus integer DECLARE @UserID integer DECLARE @PermitID integer DECLARE @Status integer
[Code] .....
It works but not the way I want it to. The @currentstatus and @newstatus are the same. I want the status before and after the update. I asked around as to how to do this and some one told me to use the Deleted table.
While my application is running on the production server, I want to develop on the test server. After a few weeks, I want to update the application, and have to update the database structure on the production server also with the most recent one from the testserver, but without deleting the current data on the production server.
I create/modify all tables in SQL Server 2005 via Management Studio. The application is built in VS2008 Pro. I'm using SubVersion (SVN).
I can let Management Studio generate scripts for every change and store them manualy, but that's a little bit too much work. What I want is a sort off version control solution. A solution that a service running on the background tracks every change on a specific database on my test server, and stores them as T-SQL code in a repository. Then I can collect all those T-SQL scripts, and run them against the production server.
I have a winform application with C# front end and sql express 05 backend.
In this database i have a table that holds manufacturer provided pricing and the manufacturers we work with update pricing constantly.
We have one table called "manufacturerpricing" which we are constantly inserting and deleting pricing records to/from to keep manufacturer pricing up to date. We may insert and delete as many as 2,000,000 records per month into this table.
This works perfectly fine and we have no problems here at all.
But with that being said, I am worried about the size of the database growing out of control due to temporary space etc. The database just keeps getting bigger and bigger.
How do I run some maintenance to keep the database size under control.
I would like to run this automatically from the C# front end so if ther is a stored proc I can call or an C# assembly I can reference that would be ideal.
I have a requirement. I am having a database which is having views,procedures and tables. Many users are having access to the database. If i want to track all the schema changes happening in the database how to achieve that? As in i have used some tables in a procedure and someone might change the column or drop the column. So it is going to have impacts on my procedure. I need a trigger which tracks all the changes done in a database and the impacts those changes are going to make.
I am an advanced user of MS Access and other databases but relatively new to SQL Server. I am utilizing SQL Server Express to analyze a database that was created by another entity. I am interested in looking at any specific record in any table and seeing who created that record, what date/time the record was created, who edited the record, when that edit occurred, etc. I don't see any such fields in the database. Would the designer of the database have to have explicitly added such functionality, or is this information available but just hidden from the novice's view?
Hi anyone please help! I have created the database driven web application with asp.net and sql server 2000. now I want keep track three operation(insert, updata and delete) that have been made on tables in a SQL Server 2000 database. what i did is: 1, create a audit table with columns: auditTable, actions, actionUser, actionTime 2, create three trigger(insert, update and delect respectivily) for every table my problem is that i can not get right user name. I use form authentication and i stored user login information in the database. every time, no matter who is logining to the web application, the action user is always SA. I user user-name() function to get userName(actionUser). Please anyone can help me to get current login user name, or tell the best way to track operations on a table. Thanks jili
We have a particular database sat on SQL Server 2012 box along with about 20 other databases.
What I require is a method/Script/Audit that will simply track anyone who logs (successfully / unsuccessful) into this one particular database on the server (The single database is the key as the end user does not want information on any of the other databases that sit on the server), it also has to log time the attempt was made and it must track the logins via SQL Server or the application itself that is attached to the database.
Is it possible to purge all records in the database while retaining the the table structures. Even better yet, could I do it on a table by table basis? If I simply delete all the records the identities for the tables do not revert back to 1.
I have a database used in SSRS in which I get a backup daily and restore it to database "DATA"
The issue I come across is SSRS has a datareader user(ssrsuser) for access to the "report store" web where users get reports
access gets erased on each restore. Can I add something to my job to restore this user after each restore or keep the settings?
declare @Bakpath varchar(80) set @bakpath = 'C:EDataExtractedDataextract.bak' Use Master Alter Database [Data] SET SINGLE_USER With ROLLBACK IMMEDIATE RESTORE DATABASE [Data] FROM DISK = @bakpath --location of .bak file WITH REPLACE GO
I am in the process of implementing database mirroring. I am trying to get an understanding about manageable ways of keeping sql user logins synced between instances.
I have looked at KB918992 which makes sense for the initial intallation, but what about keeping passwords synced in the long-term. I don't want to have to manually set a users password on the failover every time they change it.
How is everyone else accomplishing this using sql logins (not windows auth)? Pointers to articles and/or documentation would be great.
I am using Northwind database to Create a view showing every order that was shipped to Spain. Name the destination column 'DestinationSpain'. Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Here is my script:
use Northwind GO
/*STEP 2, #1*/
/* does it exist, if so drop it */ if exist (select * from dbo.sysobjects where id = object_id(N'[dbo].[OrdersToSpain]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[OrdersToSpain] GO
/* Create the View */ create view "OrdersToSpain" AS SELECT Orders.OrderID AS Order_ID, Orders.CustomerID AS Customer_ID, Orders.OrderDate AS Ordered_Date. Orders.ShippedDate AS Shipped_Date, Orders.ShipCountry AS DestinationSpain FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Orders.ShipCounty LIKE '%SPAIN%' GO
Here are the errors I am getting:
Server: Msg 156, Level 15, State 1, Line 5 Incorrect syntax near the keyword 'select'. Server: Msg 170, Level 15, State 1, Line 6 Line 6: Incorrect syntax near ')'. Server: Msg 170, Level 15, State 1, Procedure OrdersToSpain, Line 7 Line 7: Incorrect syntax near '.'.
I am trying to track unallocated space (because on my database growth is not kicking in) So using below query but not working!!! Is there any way I can track 'database size' and 'unallocated space' on a single database?
I am only DBA in my company and client want to know the growth rate of his SQL server DataBase which is in production. How can I get the growth rate per day?
Trying to track the users who are logged in and making changes to my database. I have granted permission through the Windows Groups and the log displays the group names and not the user names. Is there something to set to display the user and the group?
hi, Is there a way to view any Transact sql in a log for instance, I am iserting a new records into a table. how can I track a log for the isert statment. I know that using bcp code provides me with a log if I use /o in the bcp code. Is there any similar thing for Transact sql thanks Ali
hello,I was wondering how to track all changes on tables by using some sort of ahistory table.What i would like is a generic history table where i can see who updated,inserted, deleted or executed(stored procedures, triggers) what value inwhat table with a date when it was occured.Could somebody help me with this?
Hi all, here are my goals: Have the same DB on two different stand-alone computers, and keep them up-to-date from each other.
Basically a user would input to a DB for a week. Then every week or two, update the other stand alone DB with the new input. The DB would be exactly the same.
What are my options for this? I'd like it as easy as possible! Are there any software packages that deal with this type of transfer, etc.? Thank you!
SELECT [Issue date],DATEDIFF("dd",[Issue date],[Start date])/365 AS runningdays FROM Database1..[Insurance Policies Working DB] WHERE [Policy Number] LIKE '%1368529%'
The part 'DATEDIFF("dd",[Issue date],[Start date])' comes out as 364 if calculated on its own. However, then when it is divided by 365 it comes out as 0. How do I get it to show as a decimal instead of just rounding it down automatically? (Hope I've made sense)
A stored procedure has been executed either yesterday or today. I would like to find out which user and when they executed it. Is this possible without 3rd party transaction log interrogation tools?
What would be the best practice to follow to keep track of MS SQLserver changes... Stroed procs, tables, views, triggers, indexes, DTSand also jobs ect....I am not quite sure how Source safe works with sql server. Any otherway to do this... Even if its manual work, its okey.. I wouldappreciate if any of the DBA's let me know how they are facing thisissue....Thanks in advance...
Key, Name, Address, City, State, Zip ................ect
I would like to keep this table sorted by Name, theirfore I won't have to sort my results with every querry.
I think I need to add something to my insert to tell my table - "Hay take Jones", open up the prober place and stick him in the proper spot.
Ex: We have Appleby and Robertson in our table now. My insert would tell SQL Server to take Jones, figure our where he belongs (alpha), and stick him in, resulting in.
Appleby Jones Robertson
This way I wont have to as the querry to sort stuff every time I reference this table, this will save lots and lots of overhead. and help keep my clients happy with quick(er) response.
I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?
I'd like to keep state between calls to a UDF (mainly for caching purposes). I can shove an object into the appdomain using SetData and read it using GetData, but that requires the assembly to be set to UNSAFE. I'm confident I can secure the DB and the assembly fairly well, but I like defense in depth, and if there's another way to save state between calls to a UDF, I would prefer those.
Is there another way to store state between calls to a UDF, without putting data into DB tables or using things that will require the assembly to have such a wide permission set?
This may be more of a data design question and not an ssis question, but figured folks here could have a good idea.....the organization I'm in has the business need of collecting data from outside organizations and tracking what data is bad and what data is good. When I say bad data I mean everything from things outside of range to absolute *** - characters in integer columns, integers in character columns, special characters, etc. The data comes in in the form of flat file so it's a free for all until it hits ssis & the db engine.
Eventually of course they work to get the data corrected at the source & resubmitted but in the meantime, they have the legitimate need of not only pushing the data into the database (dirty or not), but keeping all the bad stuff. I can't in good conscience make everything a varchar to catch everything - that would go against the database gods. IMO - I still must make an integer be an integer , characters are characters, etc. But what do I do with the junk? Any thoughts?
I want to create a page(using GridView) where it will detect changes made in the database and display it for Administrator observation. I've created a table name history for this purpose. History guid (uniqueidentifier) dateCreated(datetime) lastDateUpdated(datetime) changesMade(varchar(50)) ---- eg; dropdownlist oldValue(varchar(50)) ----- compaq newValue(varchar(50)) -----dell updateBy(varchar(10)) <------ username of registered user comNo(int) = foreign key for Computer table History data will insert data whenever a changes made. Could anyone advise and direct me how to do this function. I was thinking of using stored procedure to insert the data. Thanks in advance.
