Can Logging Be Turned Off On Inserts To A Specific Temp Table From A Specific Sp?
Oct 10, 2007
I want to ship 500,000 aged transactions each night to an archive table and delete them from their source table in one or more logical units of work (LUW). Each row is approx 60 bytes and there is only one non clustered index on the source table presently.
I'm trying to weigh the pros and cons of 3 alternatives. One of them would basically insert the non-aged rows into tempdb, ship the aged records, truncate the table and then insert the tempdb records back into their source all in the same LUW.
For this alternative, I'd at least like to turn off logging when the records get inserted into tempdb as I dont see any value in logging that part of the activity. Is this possible?
I am kinda new with SQL and am trying to get a count of on the number of updates and or inserts to any given or group of tables and cannot get the syntax correct...can anyone help with this? Thank you in advance. Colin P.
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
Could you guys please help me with printing reports invoked thru command line/ URL access to print automatically to specific printers and specific trays and also is it possible to set the specific printer and tray as parameters.
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report. However, I only want to select specific data from the dataset to fill textboxes and others. I cannot change the stored procedure, but the sample of the data is shown below:-
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories. In my first table, I tried putting the cells as follows:- (expressions are highlighted in italics and bold)
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value. Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
and it give me an error The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
I am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
I`m using SQL Server 6.5 and my application does a weekly batch load of data into a database. Since it`s a re-runnable batch process, there is no need for logging, the log files just fill up, cause problems and slow the process down.
Is there a way to do an Insert into a database and have it NOT write to the transaction log?
Alternately, if I just want to dump the transaction log, is there a stored proc for doing that?
I am trying to count the number of value "99" for each column in the whole table to see how many "99" are there per column for the whole table... and get the percentage per each column.. How can I do this for the whole table?
I've an online Database on a server. I've my own user name like "AKR". I want to create every table under that user. But what happens is whenever I create a table online it goes under "DBO" user name. How to avoid that?
I am trying to count the number of value "99" for each column in the whole table to see how many "99" are there per column for the whole table... and get the percentage per each column.. How can I do this?
i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200
We are listing available SQL databases for selection in a VB list box, but need to be able to then elicit the tables in the database for another list. I am sure it is something simple, could someone help me out with this? Thanks!! Laura
Hi, we are almost finished developing our database and we have a table we want to monitor because it is getting information deleted from it and it has a delete trigger on it but we want to track the changes to the table and were wondering how to track specific changes to a user database? We want to see who is making the change, what the change they are making is and also what is the time they are making it. I have used SELECT * FROM SYSPROCESSES and I am running SQL TRACE with filter on MS SQLEW, and MS TRANS, and Visual Basic with SQL statements on tblRoute,( the table that I want to monitor) and I want to know if there is any other way to monitor this table more closely?
Hi i need to create this query. I have a table here named "Subject" from the database "Filteredtask". I want the output to show in this way:
Subject Quantity
Application Recieved count of application recieved Process Application count of process application Screen Application count of screen application Initial Interview count of initial interview Technical Exam and Interview count of technical exam and interview
I have used this code. My problem is that i put it in descending order to obtain this output, but if for example Screen application had more count it will ruin the hierarchy of the subject.
SELECT subject, COUNT(subject) AS Expr1 FROM dbo.FilteredTask WHERE (subject = N'application received') OR (subject = 'process application') OR (subject = 'screen application') OR (subject = N'initial interview') OR (subject = 'profiles assessment') OR (subject = 'technical exam and interview') GROUP BY subject ORDER BY expr1 DESC
__________________________________________________ Your future is made by the things you are presently doing.
I'm no expert at SQL, I can't figure out how to solve that problem.
The following statement:
SELECT TeamMemberId, max(MonthId) as MonthId FROM Position INNER JOIN [Month] m INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = @yearId OR y.Id < @yearId) ON m.Id = Position.MonthId GROUP BY TeamMemberId
Returns TeamMemberId MonthId 1 14 6 20
which is exactly what I want: The greates MonthId for each TeamMember in a specific year.
But I need more data for this postition, like the PositionName.
But the statement:
SELECT TeamMemberId, max(MonthId) as MonthId, PositionName FROM Position INNER JOIN [Month] m INNER JOIN [Year] y ON m.YearId = y.Id AND (y.Id = 2 OR y.Id < 2) ON m.Id = Position.MonthId GROUP BY TeamMemberId, PositionName
Returns TeamMemberId MonthId PositionName 1 2 ***. d. Geschäftsführung 6 20 ***. d. Geschäftsführung 1 14 CEO 6 16 CEO
The red rows are the ones I wanna get. How can I achieve that only the max. MonthId rows are returned, even when I need to select more columns? Any help is much appreciated.
I have slq 2005 dev ed. I have 100 tables but have a vendor that supplies me with product information in a tab delimited txt formated I need to know how to import this information into the correct table and fields can anyone help me.
I have two tables in my database: order_id with fields order (text) and comp_ID (int) and another table called customers with comp_ID (int) and company name (text) and other company information fields. The link between the two tables is the comp_ID. With every order that's made the company that made the order is stored with it in the order_id table. If I type in the order id (text), I want to be able to use the order id to search the order_id table and find out what the comp_ID of the company that made that order is. Then use that comp_ID to pull up the record of company information from the customers table with the same comp_ID. Is there some way to do this in one query? Or how do I accomplish this?
Hello. I have a somwhat simple question. I have a table inside my database where i have some columns. Id like to extract distinct values from one column and inser them into another. My table is named article and id like to slect all the destinct values from the column naned type inside my article table. Then i need to insert my values into a new table called type but i would also like the to have 2 columns inside my type table. 1 called counter witch is an auto increment PK, and another one named type where the results from my query would be inserted. Iv tried a veriety of querys but none of them have managed to do this. Could anyone help me construct this query?
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end of it a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
How can we use the SQL INSERT statement in the stored procedure to create a table in a user specified filegroup? Table1 will be created by using the following SQL script and will be placed in a default filegroup. The question is can this table been created in a specified filegroup other than default filegroup.
INSERT INTO Table1(col1,col2) SELECT col1,col2 FROM Table2
Example, userA table assign to userA filegroup. userB table assign to userB filegroup. Usually by using CREATE statement you can put the table on a specific filegroup as long as a predefined filegroup has been hard coded in the script. I appreciate any inputs.
Can you create an UPDATE TRIGGER and use some typeof code to figure out which SP just updated the current table?If not how can i achieve what i want?I tried to run SQL Profiler and i don't understand why i can'tsimply have the Profiler filter events only for the specific database idand the table's object id i chose?What am i doing wrong with SQL Profiler? I was testing thisthrough SQL EM. I had the filters chosen for a specific database idand a specific table's object id, yet when i open another table SQLProfiler captures that information too.Thank you
MSSQL Server 2000 SP3 in both houston and memphisI have a database in houston, lets call it RED. Specific tables fromdatabase RED are copied to database BLUE. Database BLUE is then backedup, ftp'd to memphis and restored. In memphis there is a single tablein this database that will not open in enterprise manager when youchoose to open all rows you get a generic ODBC timeout error no numbersjust simply "TIMEOUT".If I log into the servers in houston and open the table from databaseRED or BLUE there is no issue. I can however return up to 66,199 rowswithout an error. If i choose a number higher than this i get thetimeout error. I discovered I could run a querySelect Distinct * from f0911and it would display all my data. I am able to export the data fromthe table using this query to a csv file and reimport the data into atable and it works just fine.I built another sql 2000 server SP4 and there is no issue with thetable in the database when it is restored there. I have also triedrestoring as a different database name on the production (sql 2000 sp3)server to no avail.I can't figure out why this single table is not functioning properly inthe one instance of sql 2000.If anyone has any ideas please share them, I'm running out myself. I'mobviously very new at sql database administration and would appreciateany advice.Also, i don't believe the issue has to do with timeout countdowns.Everywhere i could change them (Enterprise manager and SQL Serveritself) they are set to unlimited if possible. I also don't see howthe problem could be related to the service pack of the sql server,seeing as although it works on the sp4 server in my possession it stillworks just fine on the sp3 server in houston.Thanks,Michael Smith
I am still learning T-SQL .Lets consider the table below, ID 1-3 shows our purchase transactions from various Vendors and ID 4-6 shows our payments to them
Table 1 - VendorTransactions
ID Â Â PARTY Â Â AMOUNT Â VOUCHER --------------------------------------- 1 Â Â Â A Â Â Â Â Â Â Â 5000 Â Â Â Â Â Â Â Purchase 2 Â Â Â B Â Â Â Â Â Â Â 3000 Â Â Â Â Â Â Â Purchase 3 Â Â Â C Â Â Â Â Â Â Â 2000 Â Â Â Â Â Â Â Purchase
4 Â Â Â A Â Â Â Â Â Â Â 3000 Â Â Â Â Â Â Â Payment 5 Â Â Â B Â Â Â Â Â Â Â 1000 Â Â Â Â Â Â Â Payment 6 Â Â Â C Â Â Â Â Â Â Â 2000 Â Â Â Â Â Â Â Payment 7 Â Â Â A Â Â Â Â Â Â Â 1000 Â Â Â Â Â Â Â Payment
Now we have a blank table Table 2 - Liabilities
ID Â Â PARTY Â Â AMOUNTÂ
I want that SQL should look for each individual party from Table 1 and Calculate TOTAL PURCHASE and TOTAL PAYMENTS and then deduct TOTAL PAYMENTS from TOTAL PURCHASE so we get the remaining balance due for each party and then add the DIFFERENCE AMOUNT alongwith PARTY to the TABLE 2 so I can get the desired result like below
ID Â Â PARTY Â Â AMOUNT ------------------------- 1 Â Â Â A Â Â Â Â Â Â Â 1000 Â Â Â Â Â Â Â 2 Â Â Â B Â Â Â Â Â Â Â 2000 Â Â Â Â Â Â Â 3 Â Â Â C Â Â Â Â Â Â Â 0 Â Â Â Â
I'm not sure what is going on but I created a table in the body of a report. I have set the RepeatOnNewPage property to be true. When I preview it the header is on all pages. Perfect! However when I print preview it or export to PDF the header is on Page 1 thru 6 it skips 7 and 8 and then prints on 9. This is happening on a few of my reports.
I have a table with 35,000 records in it. I want to update a value in column A for only the first 5000 records, leaving the value in Column A for the remaining 30,000 records as it is now. What would be the command I would use to update Column A for the first 5000 records.
I am new to this forum and had checked this forum for this problem that I am having. Although there were a lot of informative articles that I found useful, I still couldn't resolve this problem.
I am having a little trouble trying to use the Sum function to get the totals that I need for a project that I am trying to accomplish. The scenario is below:-
This is the report image that I want to produce.
Region1 policy1 policy2 AgentType1 Total Total Agenttype2 Total Total AgentType3 Total Total Region1's policy1 Total Region1 Policy2 Total
Region2 policy1 policy2 AgentType1 Total Total Agenttype2 Total Total AgentType3 Total Total Region2's policy1 Total Region2 Policy2 Total
(this is where i am having the problem) AgentTotals AllAgentType1Totals Policy1Totals Policy2Totals AllAgentType2Totals Policy1Totals Policy2Totals
TotalforallRegions RegionsTotals
I managed to get all the information I needed in the Report except for Agent Totals. I am having a little bit of a logic problem on how to get the totals for a particular agent type with each policy.
Right now my layout only has ONE grouping which holds the region types. my dataset basically is how the report is going to look like without the totals.
Is there an expression I can use that would identify what type of agent that is and then add the total ?
kinda like the following:-
=IIF(agenttype = "AgentType1", Sum(all agent totals for policy 1), "")
I am building a data warehouse. I have many columns I want to populate in a fact table using integration services. Sample fields are: companyID, companyName, companyNumberOfAccounts, company, NumberOfUsers.
In the integration services package, I would like to keep this fact table in place and populated with data and to build integration services packages that update each of the existing row's specific columns (e.g. companyNameOfAccounts) one by one. For example, I have a source of data that has companyID and companyNumberOfAccounts data.
Is it possible to use the SQL Server Destination or OLE DB Destination Integration Services elements to import companyID and companyNumberOfAccounts data so that existing records just have their companyNumberOfAccounts column updated?
