Select Into And Locking System Tables -- Am I In Trouble?
Sep 27, 2000
I am doing a ‘Select into’ to make a table from at another table which has as many as 130 millions rows (its well indexed). The new table will most often have about 1000 rows. (During the running of the app, the app will be making many new tables, since hopefully this will be a ‘popular’ item by the users. After the users ‘use’ them, they are dropped.)
I know that locks are held on various system tables (including sysobjects) during this ‘select into’ process. Are they held for the entire process?
What should I be concerned about doing ‘select into’?
I have some stored procs that have temp table creations in them using "create table #tmptable...". I have noticed that when running the procs, sp_lock shows tempdb system tables being locked. I totally expect this. But, put a PowerBuilder front end on, and I get another situation. Calling the stored procs from PowerBuilder causes the same locks to occur, but they are not released after the data is displayed through the application. Furthermore, this blocks other users from creating anything in tempdb until the user logs off of the application and ends the connection to the database.
please help - is there something I am missing in my procedures, or is this a PowerBuilder issue?
In our support we have analysis Databases (more then 20 Databases), they are getting Data from other Production serves and as well as from text file (bulk insert) and other sources like AS400. etc...
In addition, we have backup policies for these databases like full, Diff...Backups those we take by weekly (full) as well as daily (Diff), after backup we are compressing those file (link ZIP) and moving those databases to Other Backup server.
That means we are using SQL Server resources as well as system resource.
Here problem is some times (any time during the day) all of sudden my particular Drive space becoming full or System becoming very much busy...that time we cannot move even mouse courser.
I want find out
What were the reasons who are the cause for this slowdown? Or Who are the causes for this occupy the space?
I'm having an issue where a folder that I'm accessing in an SSIS package gets locked up and cannot be deleted by my package later in the process.
1) I use a File System task to rename the folder. At this point, it appears to become locked and cannot be deleted.
2) Later I attempt to delete the folder using another File System task object. I get the error '[File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process."'.
My package accesses the folder via a drive mapping. This seems to be part of the problem, as it's possible to delete the folder directly but not via the drive mapping. Even once the package completes it's still not possible to delete the folder in Windows Explorer via the drive mapping.
I found out that executing the procedure SP_INDEXOPTION and setting 'AllowRowLocks' to false i can prevent the sql server from locking rows in a table and 'AllowPageLocks' prevents from pages being locked. I need to preform same operation in case of tables. I need to perform insertion operations concurrently and acquire required locks manually. Is there a way to stop sqlserver from acquiring locks on the table. I need to disable all the locks (row, page and table).
Hi,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
I have a Table TEST (TCD int,NUMCD int ,TNM varchar(20)) where TCD is an identity column 1- When inserting I want the value of TCD to be inserted also in NUMCD. 2- I want to prevent two users to insert the same value of NUMCD if executing the same insert at the same time . For this i use the following insert :
INSERT Into TEST WITH (TABLOCK) (NUMCD,TNM) select IsNull(Max(TCD),0)+1,'Abcd' from TEST - Is the sql statment above resolve my probleme. If not is there someone who can help me resolving this probleme.
I've written a C# program which (1) Calls a SQL stored procedure which, among other things, updates a table with email information and then (2) sends the email via Outlook. This is ultimately going to be on a production server where the C# email program can be called by a number of processes.
If more than one instance of the email program is invoked at the same time, problems could occur. For instance, after the first instance updated the table it would then begin the process of creating and sending the email, but if a second instance is called at that time,it could be updating the SQL table which I think would screw up the email the first instance was creating. As a solution, I've been looking into locking tables. What I'd like to do is lock the table after it's been updated and unlock it after the email is sent. I know I can use the SLEEP function in C# so if it tries to access the table and it's locked, to wait 20 seconds or so and try again.
So how can you lock (and unlock?) a table in SQL? I'm reading about using WITH (TABLOCK) in the UPDATE query, but I'm not sure that will solve my problem. From what I can tell, TABLOCK automatically "unlocks" when the update is done. That still wouldn't resolve the issue of instance 2 updating the SQL table after instance 1 had updated it, but BEFORE instance 1 has completing creating the email (based off the data in the table).
I have a question regarding locking tables in transact sql.
The situation is the following: a table named TableA which has two columns; ID and Name. a table named IDTable which has two columns REF_ID, REF_PREFIX
in the IDTable we have the following entry: REF_ID = 1, REF_PREFIX = 'TbA'
I have a stored procedure that will make a lot of inserts into the TableA. However there is another program that makes insert into TableA as well.
They both read from the IDTable to get which ID to use (ID of course needs to be unique).
A simplified version of the stored procedure is: "SELECT all entries to move from another table" "While not all entries moved" "Get id from IDTable" "Insert into TableA" "Update IDTable" "Loop"
I would like to have it like: "SELECT all entries to move from another table" "While not all entries moved" "lock IDTable and TableA" "Get id from IDTable" "Insert into TableA" "Update IDTable" "unlock IDTable and TableA" "Loop"
and thus giving the other program a change to "get in line" to use the tables but not using them at the same time.
So my questions: 1) Is this possible (if not, please explain why)? 2) How do I lock a table, I have tried "LOCK TABLE" but that just gives me syntax error?
Hi all, I am writing an sp which includes insert and update statements. sp is working fine. But when I tried to make it as a single Transaction its not working(waiting indefinetly at second insert statement).tables are getting locked. what could be the possible reason for tables getting locked indefinetly. I Tried with set transaction isolationlevel serializable option. There are several insert into statements.some of them on the same tables again and again. any help would be greatly appreciated. Thanks.
I am totally new to locking field so I need your help.
Below are my environment details:
OS = Windows 2003 + Service Pack 1 SQL SERVER= SQL Server Enterprise Edition + Service Pack 4
My Organization is using an application that is designed in PowerBuilder. The problem is so many times this application got hang due to blocking in sql server.
I have diagnosis the problem in the below steps, Waiting spid = 192
Select * from sysprocesses Where spid = 192
Dbcc inputbuffer (192)
Then
Select blocked,* from sysprocesses Where spid = 192
Returns
201
I had repeated the steps till I got BLOCKED = 0, STATUS = sleeping and CMD = AWAITINGCOMMAND. By this way I found the blocking spid.
And DBCC INPUTBUFFER (blocked spid) is a SELECT statement.
In sp_lock spid it is showing that spid is holding a shared lock, where my application had fetched all the data and also transaction had been commited.
I cannot find the solution please advice me what to do. Why select statement is holding a lock. End users are really frustrated.
After upgrading my publishers to 2005 i noticed that i cannot specify not to lock tables during snapshot during publication creation, also not on publication properties, and i see sp_addpublication has no such parameter, is there no longer an option not to lock publication tables during snapshot?
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String) Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String) Line 57: Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.] ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56 ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743 Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
lets say i have a table that looks like thisclient date amount account1 5/2/05 100 123451 5/2/05 110 123452 5/2/05 175 223342 5/2/05 10 22334How do I select all clients that have an 80 percent difference in amount for the same account on the same day. For example, only client 2 would be selected.
Over the weekend, one of our out-of-house programmers ran an update to our three main tables. I know these are kind of broadstrokes, but basically he compared the data and updated certain fields when it met certain conditions (lots of rules basically). The three tables are one-to-one and contain a little over a million records. The comparison file contained around 400k records.
The scripts made it through 250k records from the comparison file before he had to stop it for the weekend.
When I came in to test the data yesterday - I was met with problems on my front end application - it would lock up on the write back to the database. I went into EP and experienced the same thing after making any changes to a record, it would just lock up. This only appears to be a problem on the 2 bigger tables of the 3. I currently have 12 gigs or so free on that box and I have already shrunk the log and data files.
I tried removing and re-adding the indexes, but I am freezing up everytime I try to either change or delete the Clustered Index on the Primary Key. I don't know why, but I thought maybe that was my issue.
I know this is pretty broad, but even if someone could give me ideas as to why SQL would lock up like that when trying to just save the data, it would be most helpful.
NOTE: There were NO structure changes in the update process and my restored data from Friday works perfect.
If you need more info, just ask. Thanks in advance for the help.
Server A - Oracle 10g Database Server B - SQL2005 Client PC - Sql Express
Server A holds all data. I am using a linked server to connect server A and B. I use a set of stored procedures containing the openquery() syntax to get data from Server A to Server B. These stored procedures run every 20 minutes. I then create a publisher on Server B. I subscribe from the client PC to publisher to get data down from Server B to client(Download only subscription).
When I fire up the stored procedures and attempt to replicate, everything works fine. It appears after about 4-5 hours of the stored procedures running replication begins to hang more and and more until eventually it hangs for about 10 minutes and I recieve the following error:
Command attempted: {call sp_MSreleasemakegenerationapplock} Error messages: The merge process was unable to create a new generation at the 'Publisher'. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)
I'm not sure if there is an error with openquery() locking? There is some locking activity going on with the TempDB and and Server B database. I've also come across some threads talking about the agent profiles. I'm very new to replication and very confused by all of the options in the agent profiles. Any help would be greatly appreciated!
We have transactional replication running with a seperate publisher/distributor/subscriber. I want to add a couple of articles to the publication, and then initialise them. I have added the articles and run sp_refreshsubscriptions
I now want to refresh the subscriptions. I have selected not to lock the tables on the snaphot tab of the publication properties, but whenever I run the snapshot agent it locks the application solid! Its odd, as soon as I run the snaphot agent, the phones start ringing within minutes. The application is Great Plains and I have set the snapshot agent to run nightly anyway.
Is there any way I can run the snapshot agent during working hours to refresh this one article? Once I have successfully done this, I have a number of articles want to add - but I can't lock the tables when refreshing the initial snaphot.
I faced problem related to Locking and Isolation Level on Table(s).
My problems is there r some tables which r frequently updated, and I also want to fire select query over those tables every 1 seconds and want to get only committed records. In current scenario we start transactions with ReadCommitted Lock for updating records. But in this scenario I can€™t get select query result because of some of recourses r used by transactions so after some time it gives Deadlock error.
So I want solution like both operation run simultaneously and get only committed records at a time of transaction running Please help me for solving my problem.
Hi, This seems like a basic problem but I can't figure out how to resolve it.
I have a query :
SELECT PR.WBS2, SUM(LedgerAR.Amount * - 1) AS Expr5, LB.AmtBud AS budget FROM PR LEFT OUTER JOIN LedgerAR ON PR.WBS1 = LedgerAR.WBS1 AND PR.WBS2 = LedgerAR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 LEFT OUTER JOIN LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND PR.WBS3 = LB.WBS3 WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> 'zzz') AND (PR.WBS2 <> '98') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (PR.WBS1 = '001-298') GROUP BY PR.WBS2, LB.AmtBud ORDER BY PR.WBS2
I want to sum up the middle column and last column grouping by wbs2. However, when I do SUM(lb.amtbud) the budget column is not summing correctly it is summing the column as if the data appeared like this:
I have a table where one column is named 'Category'. I'm hoping to use a DataList to display a two column table, where each cell displays a column name and the number of records in that category. For example:Marketing (15) Finances (10)Bath & Body (3) Real Estate (7) Entertainment (6) Construction (2)I tried this:SELECT COUNT(DISTINCT Category) AS NumberFROM ResourcesWHERE (Approved = 1)I get this error:Column Resources.Category is invalid in the 'ORDER BY' clause because it is not contained in either an aggregate function or the Group By clause. When I try o add the GROUP BY I get a count of one for each category which isn't what I want. What am I doing wrong?Diane
How can I write a query to return the results for a given survey for all members (including members who have not given responses) given the surveyID.
In the [SurveyQuestionMemberReponse] table I record survey results for any members who have answered the survey. However, if a member has not responsed to the survey they will not have a record in this table.
I want to return a list of members with their response to each question in the survey. If a member has not given a response I would like to indicate they have not responded to the survey and they should still appear in the list.
When I attempt to write a query to UNION the results of a query aimed at gathering all of the results in the [SurveyQuestionMemberReponse] to all of the people in the [Members] table I recieve an error when I include the questionText field in my result set.
The error indicates:
The text data type cannot be selected as DISTINCT because it is not comparable.
Can someone please point me in the right direction. I suspect I am going about this all wrong.
[NOTE] The 'surveyType' in the [Surveys] table indicates which subset of members a given Survey should be available to. For this example let's just assume that every survey should belong to all members.
I have several tables in a database which I always want to update with information from one table with new records (containing contact and demographical information). The setup is something like this:
NewRecordsTable: fn, ln, streetadd, city, emailadd, phonenumber, gender, birthdate
ContactTable: ID(primarykey), fn, ln, streetadd, city, state, zip, phonenumber, email
DemographicTable: ID(linked to primary key ID in Contact table), birthdate, gender
I want to update the ContactTable and DemographicTable with information from the NewRecords Table. What I have done so far is set the identity insert for the ContactTable to on, then inserted the fn, ln, streetadd, email, etc. from the NewTable. This works fine.
I then try to insert ID, birthdate and gender into the DemographicTable where NewRecordsTable.fn=ContactTable.fn AND NRT.ln=CT.ln AND NRT.streetadd=CT.streetadd AND NRT.emailadd=CT.emailadd - This mostly works, but the records which have NULL values any of those fields don't get inserted.
What I really want is to insert the records that have matching email addresses OR matching fn, ln, streetadd combos, but I can't figure out how to get that SELECT/WHERE statement to work.
The problem that underlies this is that I want to insert the ID values from the ContactTable into the DemographicTable, but the only way I can see to make them match properly is by matching the email addresses or fn, ln, streetadd combos from the NewRecordsTable to the ContactTable (all of the email addresses in our NewRecordsTable are unique, unless the person doesn't have an email address, in which case we make sure they have a unique fn, ln, streetadd combo)
Hey guys, I'm an old DevShed member, but my old account isn't working for some reason, so I had to recreate..
I've recently decided to learn MS SQL, and having some trouble with creating and populating tables. Using MS SQL Express 2005.
Heres the code, I keep reading through my notes on how to do it, but I cant see what I'm doing wrong. This is my first attempt at it, so there may be more wrong that I think.
Code:
drop table Property_rental; drop table Property_type; drop table Property_owner; drop table Staff; drop table Tenant; drop table Tenant_category;
Msg 547, Level 16, State 0, Line 55 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 56 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 57 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 58 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 59 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 60 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 61 The INSERT statement conflicted with the FOREIGN KEY constraint "Ten_Prop_fk". The conflict occurred in database "master", table "dbo.Property_rental", column 'ID'. The statement has been terminated.
Msg 547, Level 16, State 0, Line 82 The INSERT statement conflicted with the FOREIGN KEY constraint "Prop_Staff_fk". The conflict occurred in database "master", table "dbo.Staff", column 'STAFFID'. The statement has been terminated.
I'm trying to summarize costs assigned to active jobs for a manufacturing business. I need to aggregate work in process (WIP) cost that resides in labor-transaction and part-transaction tables based on transaction types, and transaction dates. Some transactions increase the WIP cost of the job while others decrease WIP. The business needs to see how much $$ is tied up in each job as of a particular date -- the calculation is: ToDate (cost of materials and labor assigned to job) - ToInv (cost of materials returned to inventory) - ToSales (cost of materials sold).
I developed this query incrementally and, so far, the #ToDate, #ToInv, and #ToSales temp tables seem to be populating with the correct data. My thought was to combine these three tables with a UNION and then extract the grand totals and here's where I started getting the following error: ------------------------------------------ Incorrect syntax near the keyword 'UNION'. ------------------------------------------ The problem is with the UNIONs going into #myTotal.
I would appreciate any help with this. Also, please let me know if you can suggest a better design for this. Thanks!
--M&S To Date SELECT pt.jobnum, SUM(pt.extcost) AS Cost FROM parttran pt JOIN jobhead jh ON pt.jobnum=jh.jobnum WHERE trantype IN ( <valid trans types> ) AND jh.JobReleased = 1 AND pt.TranDate < '2007-9-30' GROUP BY pt.jobnum
UNION -- This one works ok.
--L&B To Date SELECT jh.JobNum, sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs) AS Cost FROM LaborDtl l JOIN JobHead jh ON l.JobNum = jh.JobNum WHERE jh.JobReleased = 1 AND l.PayrollDate < '2007-9-30' GROUP BY jh.JobNum
SELECT pt.jobnum, SUM(pt.extcost) AS ToInv FROM parttran pt JOIN jobhead jh ON pt.jobnum=jh.jobnum WHERE trantype IN (<valid trans types>) AND jh.JobReleased = 1 AND pt.TranDate < '2007-9-30' GROUP BY pt.jobnum
SELECT pt.jobnum, SUM(pt.extcost) AS ToInv FROM parttran pt JOIN jobhead jh ON pt.jobnum=jh.jobnum WHERE trantype IN (<valid trans types>) AND jh.JobReleased = 1 AND pt.TranDate < '2007-9-30' GROUP BY pt.jobnum
Trying to convert the following SELECT statement into a INSERT statement and having trouble. No doubt this will be a piece of cake to someone. To eventually get this to a trigger stage would be nice, but for the moment I'd settle for just plain SQL. Using MS SQL 2000. The database name is reporting. The table name is CallLog. I'm trying to convert seperate date (RecvdDate) and time (RecvdTime) columns into a single DateTime column. I've scoured a lot of web pages but I'm still lost.
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
But data is needed from 3 tables... - Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID) - Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
AND THEN THERES... Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:
How to integrate the Function call: JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
into the multi-table Select relationships (while maintaining Where & Order By):
I am using ADOX to create linked tables in a jet database from an ODBC datasource. The tables in the ODBC data source does not have a primary key. so I am only able to create read only linked tables.But I want to update the records also. I tried adding a primary key column to the linked table while creating the link. but I am getting an error while adding the table to the catalog.
The error message is "Invalid Argument".
I use the following code for creating the linked table
Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)
Dim catDB As ADOX.Catalog Dim tblLink As ADOX._Table
' Name the new Table and set its ParentCatalog property ' to the open Catalog to allow access to the Properties ' collection. .Name = strLinkTblName .ParentCatalog = catDB
' Set the properties to create the link. Dim adoxPro As ADOX.Property
I want to know where I can find Object permission detailes, For example I have table1 and user "abc", I have given permission to user "abc" for table1 to "SELECT" "INSERT", "UPDATE", I want to know where this detail is stored in system table. I was looking at "SYSUSERS","SYSLOGINS", "SYSPERMISSIONS", "SYSMEMBERS", "SYSOBJECTS" I couldn't find anything, If some one can post your answers that will be great.
I have succesffuly BCP out system tables(sysdatabases, sysdevices, syslogins, and sysusages). Now I want to BCP those same tables IN to create devices, databases and so on using the BCP utility. I run the same command used to BCP out and only changes the OUT to IN but it did not work. Can somebody whose done this before tell me how to do it.
Where can I find a discussion of how to interpret the keys1 and keys2 columns of the sysindexes table? These columns are binary(255) and are supposed to contain a description of the columns to which the index applies. I also need to know how to interpret the status column of the sysconstraints column.
