The amount of columns needs to be dynamic. If i were to add pineapple there needs to be 5 columns etc.
Sorry if this has been covered before but I wasnt sure on search criteria. Thanks in advance.
Hi all, I'm having a real wierd problem with sql 7.0 (sp3)update statement. here is my table structure sku varchar(18) minimumquantity varchar(7) maximumquantity varchar(7) usa varchar(20)
when I issue an update like this...
update sapproductprices set usa= "8.25" from sapproductprices s where (s.sku= "VSE-ASAP-BB-100-S1")
it works!, but, when I change my update to this it does'nt work...I get a message which says "the command completed successfully" and no update happens
update sapproductprices set usa= "8.25" from sapproductprices s where (s.sku= "VSE-ASAP-BB-100-S1" and s.maximumquantity ="10")
Can someone shed some light on this one please... thanks uday
Hi all, I'm having a real wierd problem with sql 7.0 (sp3)update statement. here is my table structure sku varchar(18) minimumquantity varchar(7) maximumquantity varchar(7) usa varchar(20)
when I issue an update like this...
update sapproductprices set usa= "8.25" from sapproductprices s where (s.sku= "VSE-ASAP-BB-100-S1")
it works!, but, when I change my update to this it does'nt work...I get a message which says "the command completed successfully" and no update happens
update sapproductprices set usa= "8.25" from sapproductprices s where (s.sku= "VSE-ASAP-BB-100-S1" and s.maximumquantity ="10")
Can someone shed some light on this one please... thanks uday
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01
The results are just as I need:
Field01 Field02
------------- ----------------------
192473 8461760
192474 22810
Because other reasons. I need to modify that query to:
Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are:
Field02
----------------------
22810 8461760
And what I need is (without showing any other field):
Field02
----------------------
8461760 22810
Is there any good suggestion? Thanks in advance for any help, Aldo.
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ?
following part of the procedure clears my requirement.
SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E
can any one help me in this? please give me a sample query.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
strSQL = "select '<input type=text name='& id &'size=4 maxlength=4 value=>',product,description,moreinfo,media,License,unitc ost from newdeal where category='" & session("category") & "'"
Idea is the value of id to be fetched from the DB and substituted in the input box.It works well with MS Access but in SqlServer it is showing an error saying that data of varchar type cannot be converted to *******.Id is ******* data type in the DB .can anyone help me please
I'm writing a report for a survey application. There is a subscriber table, a question table, and an answer table. The subscriber table holds info like the name, address, etc of the publication's subscribers. The Question table holds the questions on the survey (ie, How many widgets do you own? What is your age? How many do you think you will purchase in the coming year?). The Answer table holds answers to each question (18-21, 25-50%, 20+, etc). I am forced to work within a very non-standard database application running on MS SQL Server 2000 that also uses a separate Relationships table, so to determine which answers go with which questions the relationships table will have a record linking a question to an answer (and vice versa). To determine how each subscriber answered the question the subscibers have a record in the relationships table linking them with specific answers (and vice versa). Both types of entries are in the same table. There are several questions that will include a note to "check all that apply" (you may get several widgets from the company). The report runs through each question, shows how many subscribers answered the question, and how many selected each answer as a percentage of the total for that question. It is nearly finished, I can run it on the test data for any date, but there is one special case I'm having trouble with. For one question, they want to know additionally how many people chose one possible answer, but no others. I can do it using simple SQL to get the list of the subscribers that chose the answer in question, and then loop through each one in vb and run another query that will tell me how many answers related to that question they selected. This works okay for the test data, but in production there are more than 100,000 subscribers, which means running 100.000+ individual querys. I know it's possible to do this in one (all I want is the final count), but the SQL for this is horrid. Any help appreciated.
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
We have an asp.net2 website pointing at sql2005 (not service packed yet - it may be the cause, not sure - would like opinions first!).The website will run without problems, but randomly we get the following scenario:We have a function that takes a SP name and a sqlcommand with parameter information. It opens a connection and uses a dataadapter to fill a datatable, then closes the connection and returns. When the problem occurs, we get a sqltimeout on the line where it tries to fill the datatable. However, when the function is called again, the results from the first query seem to get populated into the datatable. Then when it gets called again, the previous results are again returned. Its like ADO sees the timeout (but sql doesn't), but then picks up the next available resultset from sql (which is the wrong one!).Hope the above makes sense....Cheers,James.
hey ppl, if there is any1 that can help me here i would apreciateerror line(probably)Comando = New SqlCommand("INSERT INTO Table1(name) VALUES('a')", Conexao)on Table1 i have those fieldsid(int, identity) e name(text)but when i execute this code i get the following error:Error on the XML processing: no element found(ps. the error wasnt on english so i made a poor translation but i think u can have a general ideia)(the error message untranslated)Erro no processamento de XML: nenhum elemento encontradoPosição: http://localhost/teste.aspxNúmero da linha 1, Coluna 1:anyway, the wierd part is that on the sqlserver the data is insertted normally.. so.. what is that error??any1 pls help me! im going nuts (ps. im still noob on asp.net/sqlserver so please be gentle ^.^)(ps2. sorry for the crap english but it isnt my native language :) )
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
Hi,I can not find any reference or support for the following issue fromMicrosoft or on Google:When I open a table either to return data or try to use the design tools orif I try to create a new database diagram, I get a new result pane, diagrampane etc... but no result and no functionality within that pane. Thedatabase is working and I can run querys using MS Query. The problem seemsto be with MMC or Enterprise Manager.I am using SQL Server 7.623 Service Patch Level 4 or 6. I am not sure onthat point. This has been a stable well functioning system for years withno recent changes. So I am sure it is not a configuration issue.Thanks in advance for your help.PS; I don't want to seem rude, but I am not interested in buying a new"upgrade" to fix this so any MS adbots out there can just not bother withresponding to this post :)
and somtimes it failes on this command with this error:
<Message>Error in generating package C:PROJECTSPOSTALBuildSSIS packagesMappingsMappingsSBSCR_52_OUT.dtsx</Message>
<InnerException>Type 'Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask' in Assembly 'Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' is not marked as serializable.</InnerException>
<Data />
<Source>mscorlib</Source>
<HelpLink />
<StackTrace> at System.Runtime.Serialization.FormatterServices.InternalGetSerializableMembers(RuntimeType type)
at System.Runtime.Serialization.FormatterServices.GetSerializableMembers(Type type, StreamingContext context)
at System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.InitMemberInfo()
at System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Serialize(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean fCheck)
at System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serialize(Stream serializationStream, Object graph, Header[] headers, Boolean fCheck)
at System.Runtime.Remoting.RemotingServices.MarshalToBuffer(Object o)
at Microsoft.SqlServer.Dts.Runtime.ObjectHostImpl.get_InnerObject()
at Microsoft.SqlServer.Dts.Runtime.TaskHost.get_InnerObject()
at InterfaceEngine.OutputInterface.CreateExcel() in C:PROJECTSPOSTALBuildInterfaceInterfaceEngineInterfaceEngineOutputInterface.cs:line 842
at InterfaceEngine.OutputInterface.GenerateOutputPackage() in C:PROJECTSPOSTALBuildInterfaceInterfaceEngineInterfaceEngineOutputInterface.cs:line 322</StackTrace>
<TargetSite />
what is this about? why does it happen , and why not always? the package generation is generated by a web service that is called from an asp.net 2.0 page .
I setup the query as a stored procedure and changed the SSIS accordingly. Running the sp from within SSMS was fine but the task still returned the wierd results.
This is completely unexpected and giving us headaches. It doesn't even look like well-formed XML to my eyes and won't display in IE without showing problems! Where are the eroneous /'s and ROOT nodes coming from?
I can't imagine we are the only people to have run into this and I'm sure we aren't doing something quite right - just stuck as to what we're doing wrong.
Hopefully I've provided enough info. If not just ask.
Hi I'm modifying a report from MS CRM. Sales Pipeline. Not the easiest one but still. I just got to the drilldown report and need to change some things in it. I tested the SQL code as I could in SQL Server Management Studio so that I don't have any misspellings (the report I'm modifying is on a remote location). Anyway, now that I copy my code to the SQL Server Business Intelligence Development Studio and to the dataset I'm modyfing I see that a chunk of code from the bottom is missing ... ??? ... is there a limit on how much I can type?!
I counted in Word that I have 33.176 characters and 878 lines of code. Is that to much?! The original report has a dataset of 27.529 characters and 724 lines of SQL code.
I am struggling with creating a simple stored procedure, I want to take max(timekey) out in one variable @Val in a dynamic SQL statement as below in SP. When I create this SP, it creates successfully but when I run it I get error
Select @Val=MAX(TimeKey) FROM ABC
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@Val".
Can someone help me in understanding this wierd behavior? And how to get rid of this. Table and SP scripts are as below
CREATE TABLE ABC (TimeKey int, Data_Val int)
CREATE Procedure MAXVal(@Table_Name Varchar(30))
AS
BEGIN
DECLARE @Val INT,
@SQL Varchar(100)
SET @SQL='Select @Val=MAX(TimeKey) FROM '+''+@Table_Name+''
I used two symbols (”¬€¬ and ”´) in my reporting services page and they appear fine in Internet Explorer. However, when I try to export the page to a PDF file, they appear as question marks.
Can someone tell me why or how to work around this?
I am not sure which of the forums this question should be posted to (Engine or TSQL) so I have posted to both. Please forgive the cross-post.
The problem we are having is with the performance of a stored procedure. Normally, this specific proc will only take seconds to run, but if we at all modify the stored proc in any way, the proc will take at least 20 minutes to run (we don't really know when it will finish because we cut it off at 20 minutes).
When this happens, we can run the profiler and see that a lock and unlock is happening on each record that the query is returning. We are at a loss as to why this is happening.
The only way to fix it (get the proc run time back down to seconds) is to run the engine tuning advisor. We don't even need to apply any of the recommended changes, we just need to run it and the proc performance goes back down to mere seconds.
If we run the profiler again when the proc performs normally, we dont' see any locking going on.
We can consistenly repeat these steps.
Just so we aren't jumping the gun, we have re-written and disected the proc 8 ways to Sunday and it still does the same thing.
Anyone else see this and can offer any suggestions?
When I try to run the asp.net application (C#) it is throwing the following exception.
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
The problem is I don't have SQL Server 2005 installed in my system. And I am trying to connect a remote SQL 2000 server.
Adding to my confusion, the same code is working fine in another system.
I am using three tables in this query, one is events_detail, one is events_summary, the third if gifts. The original select statement counted the number of ids (event_details.id_number) that appear per event_name (event_summary.event_name).
Now, I would like to add in another column that counts the number of IDs that gave a gift who attended an event that were also listed in the event_ details table. So far I have come up with the following. My main issue is linking the subquery properly back to the main query. how to count in the sub-query and have the result placed within the groups results in the main query.
SELECT es.event_name, es.event_id, COUNT(ed.id_number) Number_Attendees, ( SELECT COUNT(gifts.donor_id) AS Count2 FROM gifts WHERE gifts.donor_id = ed.id_number ) subquery2
Yes, I am pulling my hair out on this one...................
PROBLEM:When importing from my .sql file, I loose many lines of info in my SQL table. I am using the SQL Query Analyzer to execute the file. The file contains 655,000 rows and 20 columns of data on it.
When I run the full file, the system tells me it does not have enough system resources to execute the request. I broke it down just to see if it could handle a smaller file.
When I create a table and have it insert 10,000 rows of info, it drops 88 lines of data.
When I do the same with 120,000 rows I loose 300 rows of data.
When I run it with 56,000 lines of data on the file, I loose 260 rows of data.
When I do just 1,000 rows or below it is perfect.
I have taken the chunks into Excel and verified the number of lines I was trying to put into the table, and everytime, it showed I had the amount of lines I thought I was trying to import.
When I did a query once the table was created to see how many lines it created, I was always short.
This is what my query consisted of (Let me know if this may be my issue)
SELECT COUNT(*) Col_1 FROM nfo_tablename GO
I even tried zero column names
Any suggestions??? Am I doing something wrong here? Should I be executing the process in a different way?
Here is the basic idea of what the top part of my "CREATE TABLE" file looks like (Color coded as I see it in my Query Analyzer):
Thank you all for your insight. Any ideas, thoughts or suggestions, would be appreciated. I really need to get this table done so I can get a couple web pages created this weekend that are due Monday.
SELECT DSNew, DTTM, RQDT FROM dbo.Feb INNER JOIN DMSEFL ON ACTR = DSNew where cast(DSNew as varchar(20)) = cast(ACTR As varchar(20)) If I run the above query I get zero recs back.
If I substitute a Value then I get the desired results (ie. where DSNew = '93235500') or if I enter (ACTR = '93235500') or if I put (where DSNew = '93235500' AND ACTR = '93235500')
Can anyone suggest a reason why this is happening. I know the records exist on both tables I ran the query in Acess and got the desired resutls.
