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’?
Thanks for any help,
Judith
View 2 Replies
ADVERTISEMENT
Mar 9, 1999
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?
Thanks!
Dean
View 1 Replies
View Related
Jan 25, 2008
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?
By using query analyzer from other system
Please help me regarding
View 4 Replies
View Related
Jul 4, 2006
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.
View 1 Replies
View Related
May 2, 2008
Hi,
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).
Thank you in advance.
View 9 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Aug 16, 2001
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.
Think you for helping me
View 1 Replies
View Related
Mar 8, 2005
Hi DB Gurus..
I need to lock one table ( table-level locking) as i dont want anyone to modify till i finish my stored procedure.
I need simple example on how to Lock the table and unlock..
Something like this
Lock Table Name...
...
statements go here
...
Unlock table...
Is this possible in Sql server 2000
waiting for ur replies..
Thanks in Advance
View 3 Replies
View Related
May 2, 2008
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).
Thanks
View 3 Replies
View Related
Apr 18, 2006
Hi,
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?
Thank you in advance.
BR Mikael
View 1 Replies
View Related
Apr 16, 2004
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.
View 3 Replies
View Related
Nov 17, 2006
Hello Friends,
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.
Thanks a billion in advance.
View 7 Replies
View Related
Oct 3, 2006
HI There
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?
Thanx
View 3 Replies
View Related
Oct 19, 2006
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!
View 3 Replies
View Related
Sep 12, 2005
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.
View 1 Replies
View Related
Jul 14, 2003
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.
Don
elitecobra2000@yahoo.com
View 14 Replies
View Related
Feb 1, 2008
Hi All -
My Set up:
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!
View 1 Replies
View Related
Apr 5, 2006
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.
View 1 Replies
View Related
Oct 10, 2006
Hi guys,
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.
View 10 Replies
View Related
Dec 14, 2004
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
The output of the above query:
WBS2Expr5budget
0141
0141953000
0143
121724540
1217500
1217622.5800
12171000
12172000
12174000
12174500
121772908000
121793513500
12173445018000
12176596032000
12173801044000
121838100
121913224.5
1220
1221
122262000
12224000
122312702
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:
0141
01410101410101410103000
01410101410101410147.53000
01410101410101410147.53000
0143
014305
1217
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
1217010121701012170101017.58000
121701012170101217010382.58000
12170101217010121701027.58000
121701012170101217010302.58000
12170101217010121701027.58000
121701012170101217010382.58000
121701012170101217010302.58000
1217010121701012170104958000
1217010121701012170102008000
1217010121701012170101017.58000
1217010121701012170101182.58000
1217010121701012170101952.58000
1217060
1217061
121708012170804000
So as a result I am getting 9000 where wbs2 = '0141'
I figure that in my top query I am not joining something correctly. Could someone point out what I am doing wrong?
Thank You.
:)
View 2 Replies
View Related
Mar 3, 2007
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
View 3 Replies
View Related
Nov 26, 2007
Given the following tables:
[Members]
-memberID (PK)
-memberName
[Questions]
-questionID (PK)
-questionText
[Surveys]
-surveyID (PK)
-surveyName
-surveyDescription
-surveyType (FK)
[SurveyQuestions]
-surveyID (PK/FK)
-questionID (PK/FK)
[SurveyQuestionMemberResponse]
-surveyID (PK/FK)
-memberID (PK/FK)
-questionID (PK/FK)
-yesResponse(bit)
-noResponse(bit)
-undecidedResponse(bit)
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.
Thanks,
Zoop
View 3 Replies
View Related
Jan 2, 2008
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)
Any help would be appreciated,
Thank you!!
View 3 Replies
View Related
Oct 16, 2007
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;
create table Tenant_category
(TCATID SMALLINT PRIMARY KEY NOT NULL,
TTYPE NVARCHAR(15))
;
create table Property_type
(PTYPEID SMALLINT PRIMARY KEY NOT NULL,
PTYPE NVARCHAR(20) NULL)
;
create table Property_owner
(POWNERID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20) NULL,
SNAME NVARCHAR(20) NULL,
CONTACT NVARCHAR(15) NULL,
ADDR NVARCHAR(50) NULL)
;
create table Staff
(STAFFID SMALLINT PRIMARY KEY NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20))
;
create table Property_rental
(ID SMALLINT PRIMARY KEY NOT NULL,
PTYPEID SMALLINT NOT NULL,
STAFFID SMALLINT NOT NULL,
POWNERID SMALLINT NOT NULL,
CONSTRAINT Prop_Type_fk FOREIGN KEY(PTYPEID) REFERENCES Property_type(PTYPEID),
CONSTRAINT Prop_Staff_fk FOREIGN KEY(STAFFID) REFERENCES Staff(STAFFID),
CONSTRAINT Prop_Owner_fk FOREIGN KEY(POWNERID) REFERENCES Property_owner(POWNERID))
;
create table Tenant
(TENANTID SMALLINT NOT NULL,
TCATID SMALLINT NOT NULL,
ID SMALLINT NOT NULL,
FNAME NVARCHAR(20),
SNAME NVARCHAR(20),
CONTACT NVARCHAR(20),
COMMENTS NVARCHAR(20),
CONSTRAINT Ten_Cat_fk FOREIGN KEY(TCATID) REFERENCES Tenant_category(TCATID),
CONSTRAINT Ten_Prop_fk FOREIGN KEY(ID) REFERENCES Property_rental(ID),
CONSTRAINT Ten_pk PRIMARY KEY (TENANTID, TCATID, ID))
;
Error messages I'm getting;
Code:
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.
View 6 Replies
View Related
Feb 18, 2008
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!
Below is a simplified version of my query:
--#ToDate
CREATE TABLE #ToDate (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToDate (JobNum, Cost)
--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
--#ToInv
CREATE TABLE #ToInv (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToInv (JobNum, Cost)
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
--#ToSales
CREATE TABLE #ToSales (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToSales (JobNum, Cost)
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
--#myTotal
CREATE TABLE #myTotal (JobNum varchar(14), Cost decimal (16,2), Source varchar(9))
INSERT INTO #myTotal (JobNum, Cost, Source)
SELECT d.JobNum, SUM(d.Cost) AS Cost FROM #ToDate d GROUP BY d.JobNum ORDER BY d.JobNum
UNION -- Problem**********************
SELECT i.JobNum, SUM(-1*i.Cost) AS Cost FROM #ToInv i GROUP BY i.JobNum ORDER BY i.JobNum
UNION -- Problem**********************
SELECT s.JobNum, SUM(-1*s.Cost) AS Cost FROM #ToSales s GROUP BY s.JobNum ORDER BY s.JobNum
--Select grand total for each job
SELECT JobNum, SUM(Cost) FROM #myTotal ORDER BY JobNum
--Drop temp tables
DROP TABLE #ToDate
DROP TABLE #ToInv
DROP TABLE #ToSales
DROP TABLE #myTotal
View 3 Replies
View Related
Sep 18, 2005
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.
==============
use reporting
go
SELECT RecvdDate + RecvdTime FROM [dbo].[CallLog]
===============
Any help much appreciated.
View 7 Replies
View Related
Aug 13, 2007
How Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID?
Following Function populates a field (with concat list of related titles) with other required fields:
Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000))
Returns Varchar(8000) as
Begin
Declare @Lists as varchar(8000);
Select @Lists = '';
Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier
Where @qcParent_ID = qcParent_ID;
Return Substring(@Lists,1,len(@Lists)-len(@Delimeter));
End
works perfect against single table select (returning 54 distinct rows by qcParent_ID):
Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix
From dbo.a2_qcEntryForm a
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)
This VIEW returns ALL (82) rows in table:
CREATE VIEW z_QClocate_openAll AS
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
Calling like this returns ALL 82 rows (negating the functions distinct):
Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix
From z_QClocate_openAll a
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):
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
View 3 Replies
View Related
Jun 5, 2007
Hai,
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
Dim ADOConnection As New ADODB.Connection
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")
catDB = New ADOX.Catalog
catDB.ActiveConnection = ADOConnection
tblLink = New ADOX.Table
With tblLink
' 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
adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True
adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString
adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl
End With
'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")
'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)
'Append the primary index to table.
catDB = Nothing
End Sub
If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.
Thanks in advance
Sudeep T S
View 4 Replies
View Related
Jul 19, 2000
Does anyone know what system tables are used when a sp_spaceused is executed?
Thanks in advance
View 2 Replies
View Related
May 26, 2000
I want to get rid of systemtables or systemstored procedures which displays when ever i creat new database.
How can I get rid of system tables ?
View 1 Replies
View Related
Jan 26, 2001
Hi All
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.
Thanks in Advance
Regards
Ram
View 3 Replies
View Related
Apr 15, 1999
Hello,
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.
Many Many thanks,
Ana.
View 1 Replies
View Related
Nov 29, 1998
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.
View 1 Replies
View Related
