I want to be able to see when records have been added to a table. The issue is we have a DTS job scheduled to run every night. The developer who wrote it password protected it and doesn't work here anymore. I want to add a step to this series of DTS jobs and want to run it just prior to his job. Is there a way to see when the records are being added or when this job is being run? Thanks again, you guys are the best.
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
I use below trigger to email me when a database is created or dropped.
CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT] ON ALL SERVER FOR CREATE_DATABASE AS DECLARE @bd VARCHAR(MAX) DECLARE @tsql VARCHAR(MAX) SET @tsql = EVENTDATA().value
[Code] ...
Is there a way we can get notification when a table is created or altered or dropped ?
Hi, I am working on inserting information into a DB and then retrieving the ID created for that Data to use elsewhere in my code. I have the code below but I do not know how to get toOutput parameter. Can anyone please help?
AS INSERT INTO PRODUCTION (DATEOUT,DATEREQUIRED, PREPAREDBY, COMMENTID, TOTALQUANTITY, VENDORID, WPO, TCAPONUMBER, APPROVEDBY) VALUES( @DATEOUT, @DATEREQUIRED, @PREPAREDBY, @COMMENTID, @TOTALQUANTITY, @VENDORID, @WPO, @TCAPONUMBER, @APPROVEDBY) SET @Identity = SCOPE_IDENTITY()
'collect all the information from the form and then apply all and then update 'Get a reference to the Production table. Dim dtProduction As DataTable = DS.Tables("Production") Dim dtLineItem As DataTable = DS.Tables("LineItems") ' Create the SqlCommand to execute the stored procedure. Production.InsertCommand = New SqlCommand("dbo.InsertProduction", connection) Production.InsertCommand.CommandType = CommandType.StoredProcedure ' Add the parameter for the CategoryName. Specifying the ' ParameterDirection for an input parameter is not required. 'Production.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName") Production.InsertCommand.Parameters.Add("@DATEOUT", SqlDbType.DateTime, 8, "CategoryName") Production.InsertCommand.Parameters.Add("@DATEREQUIRED", SqlDbType.DateTime, 8, "CategoryName") Production.InsertCommand.Parameters.Add("@PREPAREDBY", SqlDbType.VarChar, 50, "CategoryName") Production.InsertCommand.Parameters.Add("@COMMENTID", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@TOTALQUANTITY", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@VENDORID", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@WPO", SqlDbType.VarChar, 50, "CategoryName") Production.InsertCommand.Parameters.Add("@TCAPONUMBER", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@APPROVEDBY", SqlDbType.VarChar, 50, "CategoryName") ' Add the SqlParameter to retrieve the new identity value. ' Specify the ParameterDirection as Output. Dim parameter As SqlParameter = Production.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ProductionID") parameter.Direction = ParameterDirection.Output ' Create a new row with the same schema. Dim dr As DataRow = dtProduction.NewRow() 'you need the ID from this to insert into the Production DB ' Set the value of all the columns. dr("DateOut") = CDate(DateTimePicker1.Text) dr("DateRequired") = CDate(DateTimePicker2.Text) dr("VendorID") = CInt(vendorbox.SelectedValue) dr("HomeAddress") = txtApproved.Text.ToString dr("ApprovedBy") = txtPrepared.Text.ToString dr("TCAPO") = CInt(txtTCAPO.Text.Trim) dr("CommentID") = CommentID dr("TotalCost") = CDec(txtTotals.Text) dr("TotalQuantity") = CInt(txtQtyTotal.Text) ' Add to the Rows collection or table . dtProduction.Rows.Add(dr) 'Update the Production Table and then retrieve the ID created in this case Production.Update(dtProduction)
Hi all, we currently have an access databse with linked odbc connections to an SQL server database. Performance wise I don't think this is great. Anyone have any general information about doing an upgrade to a micrsoft project database, and what are the pros and cons of access projects compared to Access databases with linked tables. About 30 -40 users currently hammer the access front end during the day.
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
I have one table that has unique id's associated with each row of information. I want to delete rows of information in one table that have a unique ID that references information in another table.
Here is a basic breakdown of what I am trying to do:
Table1 (the table where the rows need to be deleted from) Column_x (Holds the id that is unique to the various rows of data - User ID)
Table2 (Holds the user information & has the associated ID) Column_z (holds the User ID)
I tried this on a test set of tables and could not get it to work. What I am trying to do is skip all rows of Table1 that have ID's present in Table2, and delete the rows of ID's that are not present in Table2.
Code:
SELECT Column_z FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z <> Column_x
This did not seem to do what I needed, it did not delete any rows at all.
I wanted it to delete all rows in Table1 that did not have a reference to a user ID that matched any ID's in Column_z of Table2
Then I tried another scenerio that I also needed to do:
Code:
SELECT Column_z, Column_a FROM dbo.Table2 DELETE FROM dbo.Table1 WHERE Column_z = Column_x AND Column_a='0'
'0' being the user id is inactive so I wanted to delete rows in Table1 and remove all references to users that were in an inactive status in Table2.
Neither one of the Queries wanted to work for me in the Query Analyzer when I ran them. It just said (0) rows affected.
SO when i try to load from Master table to parent and child table i am using using expresssion like
select B.ID,A.* FROM FLATFILE_INVENTORY AS A JOIN DMS_INVENTORY AS B ON A.ACDealerID=B.DMSDEALERID AND A.StockNumber=B.STOCKNUMBER AND A.InventoryDate=B.INVENTORYDATE AND A.VehicleVIN=B.VEHICLEVIN WHERE convert(date,A.[FtpDate]) = convert(date,GETDATE()) and convert(date,B.Ftpdate) = convert(date,getdate()) ;
If i use this Expression i am getting the current system date data's only from Master table to parent and child tables.
My Problem is If i do this in my local sserver using the above Expression if i loaded today date and if need to load yesterday date i can change my system date to yesterday date and i can run this Expression.so that yeserday date data alone will get loaded from Master to parent and child tables.
If i run this expression to remote server i cannot change the system date in server.
while using this Expression for current date its loads perfectly but when i try to load yesterday data it takes current date date only not the yesterday date data.
What is the Expression on which ever date i am trying load in the master table same date need to loaded in Parent and child table without changing the system Date.
Hi all, I'm writing a procedure that require to access a table from a different database but from the same server. Can the procedure able to read this table. If so how should my procedure use that table? Thank you for all your help.
hi, i am using stored procedure which put data in another table fron different database.
so i am using servername.databasename.dbo.tablename.
if I am change the servername then i have to edit the Stored procedure. Is there any way to access server name.so that if server name is chane then also i dont have to change stored procedure.
Like in c we use #define where we change value of variable that is reflected in whole program.
Hi, I have 2 databases in SQL Server named Database1 and Database2. I have logged in Database1. I would like to see the records of the Table which is present in Database2. Could anyone please tell me How to access the tables in Database2 from Database1?
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
Hi all (newbie @ asp.net)(oldie @ ASP 3)What is the purpose of using an attached MDF database files in the App_Data folder on a web site as to importing it into the SQL server directly or creating it on the SQL server. Does a mdf database attached file purely use the SQL server as a connection interface.Is it something similiar to DSN(ODBC) Connections for ms access databases.
Does anybody know of a way to determine the last date/time a table has been accessed (query/update)? I've done enough research to know that this isn't easy. However, perhaps somebody has figured out a way through some of the stats that SQL Server keeps to determine the last access of a table. I have recently been put on a team that had no DBA and has a number of databases out there. They would like to determine which databases are inactive and get rid of them. I am a developer and haven't had much SQL Server Administration experience. Any info will help greatly! Thanks!
Hi there is there any way in tsql that I can tell when a database/table was last accessed? We have a lot of *** on our servers that need cleaning up.
If it is not possible could I put a trace on the servers that will tell me if any access has occured and run this for a couple of weeks for instance to see if any access has occured?
I want to transfer ACCESS DataBase(ABC.mdb) to SQL Table Using c#.(SSIS Programming) i need to use this query on access database
which will when run create 4 fileds ABC.mdb Sorce_Db_Id_Col
Attribute_Type
Attribute_Value
Query_Flag
And sorce database Indentity_col Att_typ Att_value Att_catg
I m very new in this stuff. so please please any one have any sample code for this then send me ! please i realy need it My email id is ripal.parikh@softwaresolutionsindia.com Thanks lot!!!!
I am having problems with my store procedure displaying my last column name which is "DISPLAY_CATEGORY_DESC". I am trying to display the description from another table. But in order to get to the description I have to go through 2 tables. The tables look like something like this.
FROM di_webclickstats cwebs left join di_websessions webs on cwebs.session_id = webs.session_id where cwebs.DIR_CATEGORY_ID <> 0 and webs.ENTERED_DATE between @startDate and @endDate+1
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT TABLE_NAME ,HAS_PRIMARY_KEY ,KEY_HAS_IDENTITY_SPECIFICATION FROM ...this is where you come in ORDER BY TABLE_NAME
I need to be able to sum items grouped by op_wkctr and op_part and op_date. Here's my latest version which of course does not work.
SELECT op_wkctr, pcs, hrs.b FROM dbo.op_hist INNER JOIN ( SELECT op_part, SUM(op_qty_comp) as pcs FROM dbo.op_hist where op_type='BACKFLSH' group by op_hist.op_part
UNION ALL
SELECT op_part, SUM(op_act_setup + op_act_run) as b FROM dbo.op_hist where op_type='LABOR' group by op_hist.op_part ) AS hrs ON op_hist.op_part = hrs.op_part where (op_wkctr = 'P-P36' or op_wkctr = 'P-P38' or op_wkctr='P39') and op_date ='10/22/07'
I just made some huge changes to a database on the development computer. However, I want to transfer all of the information from the Employees table in the old DB to the new DB (no changes made on that table).
I have run into a challenge that I'm not sure how to best solve. I'dappreciate any opinions or input.I am working with a third party database. They are storing some datathat I need to use in a binary field. I've got the code to parse thebinary and reconstruct what I need. Unfortunately, there might bemultiple "entries" stored in a single binary field. a certain byte,let's just say the first, will always be the count of "entries" inthis particular SQL entry.An example:then entry might be:0x01000012341234where 12341234 is the data entry that I will parse.Another possible entry is:0x03000012341234567856789ABC9ABCThe first byte indicates that there are three data values I want toparse out:12341234567856789ABC9ABCThe portions of the binary I need are always the same length and theremay be from 1 to ~100 of them. (usually 1 if it matters.)The big question:How could a SQL query return an entry for each of the "entries" in thebinary field? For the second example I would want three entries in myresults, each row returning a different section of the binary data.For the first, only one row. I'll be querying the set and expecting toget back more results than the number of entries in the set.I haven't really tried much with this yet, (other than some mentalcalisthenics were I fell down onto the mat pretty hard), so just somedirection on where to begin would be helpful.(Yes, I understand that the difficulty is that this data shouldn't bestored this way, but there is not much I can do about that.)Thanks...James FraserJoin Bytes!
Now from various links i have established that you have to use DBCC PAGE() with relevant info to get more information, i get back a object and index id, m_objid.
However i dont know how to get the tablename form here, msdn simply says that it is an allocation id not and actual table id, so how can i from here get to table information?
All i am trying to figure out is which table is affected fromt he RID dealock, but that does not seem to be a simple process.
Does TSQL (2005) offer a way to retrieve a given table's total size (data size vs index size)? Would like to be able to get at this info using TSQL (not management studio).
Can someone write a query for me that would return names of tables that would slelect all tables of particular database and show if table has set primary key and if it has then if key has Identity Specification set to true?
something like:
SELECT
TABLE_NAME ,HAS_PRIMARY_KEY ,KEY_HAS_IDENTITY_SPECIFICATION FROM
I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net.
My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList').
I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures.
Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo.
Example of stored procedure on external database:
/****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/ CREATE PROCEDURE dbo.AddUser ( @Username Varchar(20), @Password Varchar(20) ) AS ( Select User_ID From UserList Where User_Username = @Username ) GO
Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops!
Any help would be greatly appreciated as I am completely stuck.
Can anyone help me import a ms access (97) table into a sql 2005 express database? The sql database already contains other tables but I would like to add some of my older access tables to this database without having to re-enter all the data. Please help! Thanks!
I am pretty new to SSIS, so please excuse me if this is a trivial question.
I have a denormalized database table in an Access database that I need to import into several different tables in a SQL 2005 database. You can think of the Access table as a CustomerOrders table. For example customer related information (i.e. CustomerName, CustomerID, etc...) is repeated with each record in the Access table. When this data gets moved to the SQL 2005 database, I need to insert one record for each distinct CustomerName/Customer ID record into a Customers table. I then need to insert and link every "Order" record into an "Orders" table.
I am sure that this is probably a pretty common task, but I have not found any examples or articles explaining this particular situation. What ways can this be done?
I was thinking I need to loop through each DISTINCT Customer record in the Access (source) table and insert a Customer record into the destination database's Customer table. I would then need to iterate through each row of the Access (source) table and "Lookup" the appropriate CustomerID/Key Field and insert an "Order" record.
The Access table contains over 75,000 rows of data. I am looking for the most appropriate way of doing this with SSIS (so that I don't have to write a custom application to do this!). Any help, input, links, articles, etc. is appreciated!!
