I have a job that extracts data from 10 different tables to 10 diffrenet tables of MS-Access (.mdb file).
The job ran okay since last one month but yesterday it ran and did not stop at all .Status is shown as "Executing" ..
I tried to stop it but it is not stopping . I tried to kill associated process (spid) but it allows me to kill spid but it shows there running.
Any idea why it is happening ?
If any body ahs any solution you can call me at 1 860 520 7454.
I have a package which reads from a source, does some transformation and then loads into a target table(custom transformation build to handle multiple scenarios for insert/update/delete etc). For couple of packages, I am facing an unusual problem. The package gets stuck after executing for some records. There is no data flow in the buffer. But again, if I stop executing the package and run it again, it runs through fine. Any idea what could be issue or what particularly I should be looking for in order to debug this issue.
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
Hi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
Ok I have created a 2005 sql advanced database with text indexing. I have create the database like so created a new database with text indexing enabled and the following table create table support (problemId VARCHAR(50) NOT NULL PRIMARY KEY, problemTitle varchar(50) NOT NULL, problemBody text NOT NULL, linkOne varchar(50), linkTwo varchar(50), linkThree varchar(50), linkFour varchar(50), ftid int NOT NULL) next create fulltext catalog remoteSupportCatalog create unique index ui_remotesupport ON support (ftid) then create fulltext index on support(problemBody)key index PK__support__7C8480AE on remoteSupportCatalog -------- I then populated some rows and issues a quesry Select * from support where freetext(problemBody, 'test database') it works pulls back all the data I expected it to pull back
In my asp page I created a database connection with the folling select command <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:rsdb2ConnectionString2 %>" SelectCommand="SELECT * FROM support WHERE FREETEXT(problemBody, @srchBox)">
created the search parameter<SelectParameters> <asp:ControlParameter ControlID="srchBox" PropertyName="Text" Type="String" Name="srchBox" /> //this is a text box that is searchable with a button </SelectParameters> and it doesnt give me back an error or data it does nothing. What am I missing?????
I hate making complecated queries....but this doesn't seem too hard and I cannot figure out where my error is. The message is "Incorrect syntax near the keyword 'FROM'."
The query is: SELECT tblUsers.lUserID GROUP BY tblUsers.lUserID HAVING Count(tblDLHistory.lDownloadID) = 0 FROM tblUsers LEFT JOIN tblDLHistory ON tblUsers.lUserID = tblDLHistory.lUserID WHERE tblDLHistory.lVersionID = 5
I need to search the database and pull up all customers who have a 'device' and their email address. I have watered down my select statement, but the following is the basics of it. I just cannot figure out how to also append the email. I have tried many different attempts and have come up dry.
Select a.company From dbo.contact1 as a, ( Select DISTINCT accountno From dbo.contsupp Where contact LIKE 'Product Inventory' AND contsupref LIKE '%device%' ) as b Where a.accountno = b.accountno
Below are some sample databases to get an idea of some possibilities. There will not always be an entry in dbo.contsupp for an email address. There will not always be an entry in dbo.contsupp for a device.
I have a small table to manage orders in my company. When a new order is entered, the script makes use of the last row of the table to find out the last order, increments it and creates the new order number. The problem is, a few days ago the last row got stuck. New insertions to the table all got the same order number and are placed above the last row. Anybody has any idea what's going on?
Heres is a shorten downed example of what I am trying to achieve. I have four tables. The first being OrderHeader, the second OrderLines, the third StockCategory. The OrderHeader table contains basic order details, which in turn is linked to the OrderLines table which show if the order in the OrderHeader has a single or multiple order lines. The StockCategory table shows what stock group the item in each order line is associated to.
Here is an example of the tables in a shorten downed version (in both data and fields):
OrderHeader: Sales Order Ref, Order Date, 1, 01/05/2008 2, 01/05/2008 3, 02/05/2008 4, 02/05/2008
OrderLines: Sales Order Ref, Part Number 1, 222 1, 234 1, 333 2, 222 2, 555 2, 444 3, 333 3, 111 4, 222
StockCategory
Stock Category, Part Number, A, 222 B, 234 C, 333 D, 444 E, 111
The thing I am trying to do is assign each of the orders to a Stock Group which I can do. The thing that has baffled me is if an two of the order lines in one order are assigned to different stock groups. If this occurs I want to assign the order to the order to the stock group with the highest priority (1 being highest, 3 being lowest) for example if one order line in the order was assigned to Berr (priority 1) and the other to DFID (priority 3) the order would be assigned to Berr.
SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT COUNT(oh.[Sales Order Reference]) FROM dbo.OrderHeaders AS oh LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference] LEFT JOIN dbo.StockCategories AS sc ON ol.[Part Number] = sc.[Part Number] WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate AND sc.[Stock Group] IN (SELECT sg.[Stock Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'Berr') AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference] FROM dbo.CancelledOrderLines AS col WHERE col.[Part Number] = ol.[Part Number])
Been spinning on this for whole weekend I can't seem to get what you I want. I have the following xml result from my query. As you notice one of the child elements has the tag identifier VJobs, how can I make it so it says 'task' instead?
Here is the query, which details jobs to be done on different equipments SELECT EquipmentID + 1 as id, EquipmentDescr as [name], '#99ccff' AS color, 'true' AS [expand], (SELECT JobID + 2AS id, 'Layout#' AS [name], '#99ccff' AS color, (SELECT [taskproperty-id] AS [taskproperty-id], [value] AS [value] FROM dbo.JobDetails customproperty WHERE customproperty.JobID = VJobs.JobID FOR XML AUTO, TYPE) FROM VJobs WHERE VJobs.EquipmentID = task.EquipmentID FOR XML AUTO, TYPE) FROM VEquipments task ORDER BY EquipmentDescr FOR XML AUTO, TYPE
Hello, Well actually I am beginner to ASP.Net, I am working on Microsoft Virtual PC with VS 2005 and SQL Server 2005 installed. Now when Create web application in Studio all works fine with databases but when i try to write some code for accessing SQL Databases I keep getting this error PLZ heLP.Following is my code<% @ Import Namespace="System.Data" %><% @ Import Namespace="System.Data.SqlClient" %><html><script language="C#" runat="server" Debug="false" >SqlConnection sqlcon;protected void Page_Load(Object Src, EventArgs e){ sqlcon=new SqlConnection("Data Source=VS02005;Initial Catalog=Gaurav;Integrated Security=True"); SqlDataAdapter sqlcom = new SqlDataAdapter("select * from Employee", sqlcon); DataSet ds = new DataSet(); sqlcom.Fill(ds,"Employee"); DataGrid1.DataSource = ds.Tables["Employee"].DefaultView; DataGrid1.DataBind();}</script><form runtat="server"> <asp:datagrid id="DataGrid1" runat="server" /></form></html>This is the error i am gettingServer Error in '/' Application.--------------------------------------------------------------------------------SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. 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.Data.SqlClient.SqlException: SELECT permission denied on object 'Employee', database 'Gaurav', schema 'dbo'. I am stuck dont know what to do, I hve checked all permissions for user gaurav, I also gave administrator rights to gaurav but nothing is working PLEASE help me.
Sorry for all the code below. I am realizing that my DB design is bad but I already have 7 pages built around it that work fine...until now, so I would really like to not change the DB if possible. My table has 22 columns: iID which is the identity colum. Then there is iAsmtID which is the assessment ID. Lastly there are 20 colums- q1 through q20, each of which will have a 1, 2, or 3, depending on the radio buttons the user clicked. Nows my problem. I have to find a percent for the assessment. It works like this. 3s are NA so we are not worried about them now. I need to find the number of ones and the number of twos for each assessment ID. Then add those together and divide by the number of ones. How can I find the number of the ones and twos. I have below but its not working. Says there is incorrect syntax at the ',' which is a different color below. Any and all help appreciated.'Open connectionset conn=Server.CreateObject("ADODB.Connection")conn.open My_Connset rs = Server.CreateObject("ADODB.Recordset")str = "SELECT SUM((CASE WHEN q1=1 THEN 1 ELSE 0 END)+(CASE WHEN q2=1 THEN 1 ELSE 0 END)+(CASE WHEN q3=1 THEN 1 ELSE 0 END)+(CASE WHEN q4=1 THEN 1 ELSE 0 END)+(CASE WHEN q5=1 THEN 1 ELSE 0 END)+(CASE WHEN q6=1 THEN 1 ELSE 0 END)+(CASE WHEN q7=1 THEN 1 ELSE 0 END)+(CASE WHEN q8=1 THEN 1 ELSE 0 END)+(CASE WHEN q9=1 THEN 1 ELSE 0 END)+(CASE WHEN q10=1 THEN 1 ELSE 0 END)+(CASE WHEN q11=1 THEN 1 ELSE 0 END)+(CASE WHEN q12=1 THEN 1 ELSE 0 END)+(CASE WHEN q13=1 THEN 1 ELSE 0 END)+(CASE WHEN q14=1 THEN 1 ELSE 0 END)+(CASE WHEN q15=1 THEN 1 ELSE 0 END)+(CASE WHEN q16=1 THEN 1 ELSE 0 END)+(CASE WHEN q17=1 THEN 1 ELSE 0 END)+(CASE WHEN q18=1 THEN 1 ELSE 0 END)+(CASE WHEN q19=1 THEN 1 ELSE 0 END)+(CASE WHEN q20=1 THEN 1 ELSE 0 END) AS [color:#FF0000]CountOfOnes,SUM[/color]((CASE WHEN q1=2 THEN 1 ELSE 0 END)+(CASE WHEN q2=2 THEN 1 ELSE 0 END)+(CASE WHEN q3=2 THEN 1 ELSE 0 END)+(CASE WHEN q4=2 THEN 1 ELSE 0 END)+(CASE WHEN q5=2 THEN 1 ELSE 0 END)+(CASE WHEN q6=2 THEN 1 ELSE 0 END)+(CASE WHEN q7=2 THEN 1 ELSE 0 END)+(CASE WHEN q8=2 THEN 1 ELSE 0 END)+(CASE WHEN q9=2 THEN 1 ELSE 0 END)+(CASE WHEN q10=2 THEN 1 ELSE 0 END)+(CASE WHEN q11=2 THEN 1 ELSE 0 END)+(CASE WHEN q12=2 THEN 1 ELSE 0 END)+(CASE WHEN q13=2 THEN 1 ELSE 0 END)+(CASE WHEN q14=2 THEN 1 ELSE 0 END)+(CASE WHEN q15=2 THEN 1 ELSE 0 END)+(CASE WHEN q16=2 THEN 1 ELSE 0 END)+(CASE WHEN q17=2 THEN 1 ELSE 0 END)+(CASE WHEN q18=2 THEN 1 ELSE 0 END)+(CASE WHEN q19=2 THEN 1 ELSE 0 END)+(CASE WHEN q20=2 THEN 1 ELSE 0 END) AS CountOfTwos FROM ITCC_Test WHERE iAsmtID="&iAsmtIDresponse.Write(str)rs.open str, connif rs.eof = true then ' response.Write("<h2>No count done</h3>") response.End()else'Declare variables CountOfOnes = rs("CountOfOnes") CountOfTwos = rs("CountOfTwos")end ifrs.closeset rs = nothingconn.close'set conn = nothing
I have a table with 4 relevant fields (blank lines added for clarity). State, City, Name, Primary_Contact IL, Springfield, Bill, n IL, Springfield, Frank, n IL, Springfield, Larry, n
IL, Bloomington, Steve, n IL, Bloomington, Chris, y
IL, Chicago, Betty, n IL, Chicago, Linda, n IL, Chicago, Sue, n
I need a query to return the state and cities that don't have a Primary_Contact='y' So the results would be: IL, Springfield IL, Chicago
I need the results from the following query to be with the results of the second query. Any ideas?
Code:
SELECT PR.WBS1, PR.WBS2, PR.WBS3, PR.Fee, PR.ConsultFee, PR.ReimbAllow, PR.LongName, PR.Name, CL.Name AS CLIENTNAME, CLAddress.Address2 AS CLIENTADDRESS2, CLAddress.Address3 AS CLIENTADDRESS3, CLAddress.Address4 AS CLIENTADDRESS4, CFGMain.FirmName, CFGMain.Address1, CFGMain.Address2, CFGMain.Address3, CFGMain.Address4, Contacts.FirstName + ' ' + Contacts.LastName AS CONTACT, LB.AmtBud, LB.BillBud
FROM PR LEFT OUTER JOIN Contacts ON PR.ContactID = Contacts.ContactID LEFT OUTER JOIN CL ON CL.ClientID = PR.ClientID LEFT OUTER JOIN CLAddress ON CL.ClientID = CLAddress.ClientID LEFT OUTER JOIN LB ON LB.WBS1 = PR.WBS1 AND PR.WBS2 = LB.WBS2 AND LB.WBS3 = PR.WBS3 CROSS JOIN CFGMain Where pr.wbs1 = '001-298' and pr.wbs3 != 'zzz'
and
Code:
SELECT * FROM LD WHERE (BilledPeriod = '200408') AND (WBS1 = '001-298')
I have a sql problem I'm trying to solve. I'm selecting from a table based on a foreign key, and the select returns 2 rows. The table has a column called type, and each row for the foreign key has a different type value. Hopefully the example below can help to explain:
Case 1:
PK | FK | Type | Text -------------------------- 1 | 226 | 0 | some text goes here 2 | 226 | 1 | NULL
Case 2:
PK | FK | Type | Text -------------------------- 3 | 334 | 0 | some text goes here 4 | 334 | 1 | actual text I want to select is in this cell
I'm trying to create a select statement to grab the text for the foreign key I'm looking up. In case 2, I want the text where type=1 but in case 1 I want the text where type=0.
I had started writing it as
select text from table where fk=334 and ( (type=4 and text is not null) or type=0 )
but this returns both rows. What I what is something that I think is more akin to
case a || case b
expression in programming - if case a evaluates as true, use that, otherwise evaluate case b and use if true, otherwise return false.
I hope you can understand what I'm trying to get and any suggestions would be much appreciated.
Everything works except for the select portion, I cant put my finger on what is wrong with this.. I included the trigger and error.. If one can show what I did wrong that would be great..Thanx..
SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[arcit] ON [dbo].[active] AFTER UPDATE as begin begin tran
SET IDENTITY_INSERT Archive ON INSERT INTO Archive() SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'archive' order by ordinal_position fromInserted i inner join deleted d on d.grid = i.grid inner join [Active] a on a.grid = i.grid WHERE i.arc = 1 and isNull(d.arc,0) != 1
DELETE FROM a fromInserted i inner join deleted d on d.grid = i.grid inner join [Active] a on a.grid = i.grid WHERE i.arc = 1 and isNull(d.arc,0) != 1
commit tran end
Error Message
Msg 156, Level 15, State 1, Procedure arcit, Line 10 Incorrect syntax near the keyword 'from'.
I am attempting to write a function to Check the price of every disk and return the number of titles ont he disk which has the highest price. I do not even think that I am in the ballpark:
CREATE FUNCTION highest_price ( )
BEGIN
SELECT count(*) AS FROM Disk WHERE price = (SELECT max(price) FROM Disk);
document_area: doc_area_id(int) and doc_area_name(string). document_area_access: doc_area_id(int) and username(string).
I am trying to do a select statement in an sqldatasource in .net that will select all the document_area.doc_area_name's where the current users username is in the document_area_access using the doc_area_id to link the tables.
I'm trying to load in the code to a tutorial that requires SQL Server. Step number one on the install instructions says:
Set Up The Database
You may choose to set up your own Northwind database or restore the included Northwind.bak. Regardless of which approach you take you will need to grant your local [MachineName]ASPNET account access to Northwind and then execute GenerateStoredProcedures.sql against it to create the stored procedures. If you receive a message "There is no such user or group 'aspnet'." you may need to search and replace "ASPNET" with "[your machine name]ASPNET" I'm using SQL Server Express and the SQL Server Management Studio Express, and well...I'm already stuck. I'm trying to create a new user named ASPNET from the management Studio. I don't see any UI that mentions creating user accounts, and the closest thing is LOGIN accounts. So I created a ASPNET login account, and the disconnected from SQL Express and tried to login as ASPNET. When I do this, I get an error that reads:
Login failed for user 'aspnet'. The user is not associated with a trusted SQL server connection. ERROR: 18452 suprisingly, every SQL Express tutorial that I could find never seems to mention anything about creating a user account. Can someone clue me in to what I am doing wrong? Or point me to a SQL Express tutorial that explains user accounts or logins so I can move on to step #2. Thanks, -=Me=-
most my fields that I am interested in getting percentage values for have to be counted first.... I am having problems converting the counted values to percentages. I don't think I am doing it right...
I would greatly appreciate an example of a very simple matrix table with a counted value that displays percentages. I have found examples online but are part of a much more complex table and I can't seem to get thier formating to work on mine.
i want to write a database trigger to increment the salary by 10% for technicians who have done three tests on a particular date.
there are two employee types.(1)technicians (2)traffic controllers. employee category is defined in "Type" attribute of Employee table. the increment should happen only to technicians.thank you in advance.
I know that the answer to my problem is something that lies right infront my eyes but I got totally stuck. I cant understand why, when I deploy a database application I've made, when the application is run for the second time, the database doesn't have the data I have entered the first time... Is it something I have to tell my application?
It must be something trivial but I just can't put my finger on it.
So, the main objective for this database/web application is reporting. The issue/situation is:1. The customer has 10 goals2. Each goal has at least some detail shown in bullet points via a word document...this is how we'll get the data at first. we're going to develop the UI of the web application after the customer has more specs...main objective, get web reporting!3. Each detail could be broken down into a further / 2nd level of detail4. The 2nd level of detail could be further detailed by a 3rd level ...and, not knowing how in depth they'll provide the detail, there will be activities associated to points 2, 3, or 4...depending on how deep/specific the detail. So, you might have one goal of "build a fence". And this goal only goes to the first detail level of say, "make it stucco" and that's it...no other detail. A second goal might be, "landscape the front yard", and it's broken down into further levels of 2nd and 3rd:1. Make stepping stones**Three 2nd levels of:--1. Make path curvy--2. Use flagstone--3. Lead up to front door2. Plant trees**Three 2nd levels of:--1. Use 5 Aspens--2. Use 2 dogwoods--3. Use 1 maple3. Fill in bare spots**Two 2nd levels of:--1. Use bermuda*****example of 3rd level--2. Ensure dirt is fertilized----1. Use Miracle grow----2. Must be via hose, not granular Now the tricky thing I'm stuck on is that be it the 1st level of detail, the 2nd, or the 3rd level, each could have activities. So, if the detail stops at the 1st level, activities will be tied to the 1st level. If detail stops at the 2nd level, activities will be tied to the 2nd level...and so on. I was wondering about a detail table coming off the 10 goal/master table which would have columns of:1. Goal_ID2. Detail_Level_ID3. Detail_Level_Desc4. 2ndLvlDetail_ID5. 2ndLvlDetail_Desc6. 3rdLvlDetail_ID7. 3rdLvlDetail_Desc What I'm wondering is how do I link the activities? Should I just have a 3rd table, "activities", and it would have:1. ActivityID2. ActivityDesc3. LinkID...and LinkID could be either tied to Detail_Level_ID, 2ndLvlDetail_ID, or 3rdLvlDetail_ID This way I'm not too sure about referential integrity via the DB diagram to cascade deletes & updates because Detail_Lvl_ID could be non-unique. Another option would be to have 4 tables:1. 10 goal main table2. 1st level of detail table3. 2nd level of detail table (links to 1st level)4. 3rd level of detail table (links to 2nd level) ..and the activites table would now have to be split into three different, i.e. DetailAct, 2ndDetailAct, 3rdDetailAct. Right? Any suggestions are welcome. Like I said, reporting is the main concern at first, and the customer wants to be able to either show/hide activities, so sub-reporting to the detail, 2nd lvl, 3rd level for the activities shouldn't be too bad. Thanks!
Hello everyone..... First of all i have to say that a network engineer not a programmer. So here is my question..... A contractor built a web application in visual studio 2005 and sql server 2005 expess. It is on a windows xp pro developement box.... and the site runs in VS2005 but when it is put into IIS i get SQL errors and nothing runs. Can anyone help me sort out this issue because it has been dumped in my lap and i'm clueless....... when you goto the site you are supposed to be able to loging to a server page ( not windows Authentication) however, when you enter a name and password to log in it returns a sql error. i'm really getting tired of this issue so any help would be appreciated. I have included the SQLexpress error below.... thanks in advance!
Server Error in '/' Application.
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. 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.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
I have log shipping set up between 2 servers. It works fine for 4 db's but will not work for the 5th. I have removed and recreated the log shipping again for this DB. (THe DB is actually not being used in production yet) What happens is the complete setup goes fine and the process starts. I come back after a few hours and notice the job is failing. WHen I go to the monitor side it shows the copy and restore work fine. The problem is that it keeps doing the file named db_name_initial_file.trn over an over again. Even though new files are being copied over, they are never restored, it just gets stuck on this one. Anyone seen this before?
I want to return a list that contains each employee's ID, the date of their last payrate adjustment, and their current payrate. Note that in the table below, employee 1002 was a bad boy in March of 2005, and his hourly rate was reduced to 14.00.
Hey guys, I am stuck on a homework question and I was hoping someone could help point me in the right direction.
the question is:
"find the book code and book title for each book whose price is greater than the book price of every book that has the type HOR"
now, I know how to find the book price of every book that has the type HOR but how do i minimize the results to show all books that have greater prices that the HOR books?
Basically i need to change all fac_id which = 1 and change them to fac_id = 2, this is simple enough:
UPDATE SIC SET SIC_id = 2 WHERE SIC_id = 1
How ever, i need an exception where the 'id' is the same so i don't get duplicate entries.
So from the table above i need to change 2347 fac_id = 1 to fac_id = 2. I would NOT how ever change 1234 fac_id = 1 to 1234 fac_id =2 because there is already an id number with the same fac_id.
SELECT aircraft_id, maximum_speed_mph FROM us INNER JOIN rus ON us.aircraft_id = rus.aircraft_id WHERE maximum_speed_mph > ‘1000’
This is my error:
Msg 209, level 16, State 1, Line 4 Ambiguous column name ‘maximum_speed_mph’ Msg 209, level 16, State 1, Line 4 Ambiguous column name ‘Aircraft_id’ Msg 209, level 16, State 1, Line 4 Ambiguous column name ‘maximum_speed_mph’
Thanks for the help
US Navy - We are fueled, armed, and go for launch.
