Hi, I execute a select statement
SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'
every times I execute it I get different result.
1st result : 17036986
2nd result : 17037903
3rd result : 17038309
Any idea??? There is no any inserting on TABLEA, so it should not has changes. Is that because of statistic is still updating?? I don't have much knowledge on this so I cannot sure the cause of the problem. Please advise.
Hi,I have this code following my signature to SELECT a number.I don't know what am I missing, because it always return the number which the variable banID was initialized (32 in this case).The strangest thing is that if I run the statement inside MS SQL SERVER, I get the right result.Any help would be appreciated.Warm Regards,Mário Gamito--// Get inserted user IDint banID = 32 ;SqlConnection myConn = new SqlConnection("Data Source=192.168.1.6; Initial Catalog=db1; User=sa; Password=secret");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlCommand myCommand = new SqlCommand("SELECT MAX(PublisherID) FROM table1", myConn);banID = Convert.ToInt32(myCommand.ExecuteScalar);}catch (Exception e){ Console.WriteLine(e.ToString());}Response.Write(banID);
View 3 Replies View RelatedI want to export the data from a database and place it on a csv file to be imported to another database. However, I want some columns from the selected data (result of select statement) to be encrypted. What should I do? Is this possible? How do you decrypt the data during import?
The original data is from an MS SQL database to be transferred to a PostgreSQL database.
MS SQL ----- CSV (some columns are encrypted) ---- PostgreSQL (all columns decrypted)
I posted the same question on the mysql category. I wanted to delete it since I just realized today that I posted it on the wrong category but seems like there is no way I can delete.
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
Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.
currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.
Problem:
i need to call an aggregate count on the results of another aggregate function (sum) with a group by.
*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales
This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.
thanks,
Tom Anderson
Software Engineer
Custom Business Solutions
I want to update records in 1 table with the result of a select statement.
The table is called 'MPR_Portfolio_Transactions' and contains the following fields:
[PTR_SEQUENCE]
,[PTR_DATE]
,[PTR_SYMBOL]
,[PTR_QUANTITY]
,[PTR_ACUM]
And the select statement is like this:
SELECT SUM(PTR_QUANTITY) OVER (PARTITION BY PTR_SYMBOL ORDER BY PTR_DATE, PTR_SEQUENCE) AS 'ACUMULADO'
FROM MPR_portfolio_transactions
ORDER BY PTR_SYMBOL, PTR_DATE, PTR_SEQUENCE
This select statement generates one line per existing record. And what I would like to do next is to UPDATE the field 'PTR_ACUM' with the result of the 'ACUMULADO'
the key is PTR_SEQUENCE
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.
Table 1
Org
aaa:aaa:aa
aaa:aaa:ab
aaa:aab:aa
Table 2
delim
Seg1Start
Seg1Len
Seg2Start
Seg2Len
Seg3Start
Seg3Len
:
1
3
5
3
9
2
My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.
Any ideas? Can this be done in SSIS?
I'd really appreciate any insight that anyone might have.
Regards,
Bill
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?
View 3 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
Hi,
What I would like to do is run a select statement, and then have the result of the select be used/run thru the same select again untill it hits a certain where clause.
This is for a where used search in our BOM (Bill of Material) table.
So for example
BOM 1
level 1 Item# 123 type Finished Good
level 2 Item# 45 type subassembly
level 3 Item# 56 type purchased item
BOM 2
level 1 Item# 678 type Finished Good
level 2 Item# 78 type subassembly
level 3 Item#56 type purchased item
So my input into the select statement would be Item# 56, it will then first find Item# 45 and Item# 78, but those I am not interested in. I want the select to then take Item#45 and Item#78 and run it thru the same select so that it will find Item#123 and Item#678.
The select will know to stop there since these are flagged as "Finished Good" in our system, so they will not be in any other BOM. This is what I would imagine being specified in the "where clause".
I can write the first part, where the select will find Item #45 and Item#78, but don't know how to get past that point.
Any help or suggestions are greatly appreciated!!
Thanks,
Michiel
I am using MSDE and WebMatrix. My stored procedure is creating a Dynamic SQL query and is is about 200 lines long.
I am not getting the expected results, but also not generating any errors. I inserted a Print statement to print the resultant SQL query, but I don't know how to see or display that print result.
I do NOT have SQL2000, only MSDE. I am using WebMatrix and VB.net to create my application. Is there some class in asp.net that will help me, or some free utility. One of the problems is that the dynamic SQL is using over 20 parameters to create the query; the end result of the user picking fields on the webform.
I am using MSSQL 2000, SQL Server Developer Edition and product version is : 8.00.760 (SP3)
Application Server: JBoss v 4.0.4
Driver : sql 2005 driver.
Hi,
I am running my application in JBOSS and hibernate to do all the query to the DB. While we are testing our application, it is working fine for the 1st day of stress testing, however after running for one days, it starts throwing the following exception. This can occur in a number of method call with no fixed pattern. Anyone has any idea what is going on?
used by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:392)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:333)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1114)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:756)
at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:63)
... 88 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PreparedStatementExecutionRequest.executeStatement(Unknown Source)
at com.microsoft.sqlserver.jdbc.CancelableRequest.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
at org.hibernate.loader.Loader.doQuery(Loader.java:662)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 97 more
Hi everyone,My brain refuses to remember the (undocumented?) stored procedure I'mthinking of. It takes at least two parameters: a sql statement toexecute, and a table name (or something of that nature).Then, for each value in the table, it executes the sql statement andpasses the value as a parameter.Can anybody refresh my memory? The functionality may be slighlydifferent than described, but the principle is the same. Thanks verymuch...-Joe
View 3 Replies View Relatedhere is my update statement in a stored procedure:
create proc proc_add_comp
@comp_answer nvarchar(300),
@admin nvarchar(100),
@comp_id int
as
update tbComp set
comp_answer = comp_answer + ' - ' + @admin + ', ' + @comp_answer
where comp_id=@comp_id
then I try it like this :
exec proc_add_comp 'new answer','by me',1
result is : (1 row(s) affected)
but when I look in the db, nothing was changed, comp_answer still has its old value..
comp_answer is nvarchar type column..isnt add operation allowed in update statement?
thanks...
The query is (Select (25/20*100))
The wrong giving result is 100 it's should be 125
How I can use a query statement to get a correct result?
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
View 5 Replies View RelatedOk 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
View 2 Replies View RelatedHi guys,
I have the query below (running okay):
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.
How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked" ?
Thank you.
CREATE VIEW dbo.vwu_ReportViewASSELECT EmployeeID , LastName , FirstName , LocationCode , UserID , Today , Login , AMBreakOut , AMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS AMBreak , LunchOut , LunchIn , CASE WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 66 THEN '0' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 67 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 81 THEN '15' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96 THEN '30' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 97 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 111 THEN '45' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126 THEN '60' ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon ,(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked
Hi all,
I want to save DBCC's result into a table to execute my statement but I don't know how to, exmple for DBCC OPENTRAN (my database) - how can I put SPID & UID to my variable from this statement?
Thanks in advance
Happy New Year
PTH
Gurus.I do not know if it is possible, but here is what I want to do.I want to allow user to page the SQL result, so he could decides toreturn from row 10 to row 20, or row 100 to 200, without returns thewhole resultset. Every time he sends another request, I do not mind tohit the database again, (I do not want to cache the result in themiddle tier server, scalability issue), and I know that I could achievethis with CURSOR, but unfortunately the FOR XML is not allowed in aCURSOR statement .(I know that I could achieve what I want to do by writing custom codein the middle tier, but I just want to see if there is a way to do thison the database side.)Any comments & suggestion is greatly appreciated.Thanks in advance.(I am using SQL2005)John
View 2 Replies View RelatedI'm trying to migrate to the 2005 JDBC driver against SQL Server 2000, but I'm getting the following exception: com.microsoft.sqlserver.jdbc.SQLServerException: The statement did not return a result set. This is similar problem to a past thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=413019&SiteID=1
The difference is that my stored procedure uses cursors. This same stored procedure works fine with the 2000 JDBC driver.
Does anybody have any ideas how to resolve this problem?
Hi,
When a record is to be inserted, how can I check whether the insert is succeeded or not?
CREATE TRIGGER MY_TRIGGER ON MyTable AFTER INSERT
AS
DECLARE @id INT
DECLARE @name NVARCHAR(50)
SELECT @id=id,@name=name FROM INSERTED
BEGIN
INSERT INTO MySubTable(id,name) VALUES(@id,@name)
-- if failed, rollback
END
GO
GO
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.
Thanks and Regards,
Kiran Suthar
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
In the database i have a table with this information:key_id =1key_desc =43+34+22+12I want sum the values in key_desc. Something like:SELECT key_desc FROM tableBut the result of the select was "111" and not "43+34+22+12".Is this posible?
View 6 Replies View RelatedHi there,
Problem:
Having an old webapplication with dynamic build sql queries. Now the database changed completly because of new db model. Queries are getting much more complex now, so i put them into stored procedures.
Now i want to work with the data in an application for sorting an filtering. All the 'WHERE' conditions are build dynamic within the code. For not modifying the whole application i would like to do something like:
SELECT * FROM (EXECUTE sp_someprocedure) WHERE X = 1
Is there a way to realize this ?
Thx for helping
hey there
I have parameters in my report set up
CallStatus = string
Available Values are non-queried and values are listed in the value list
closed, open, pending, select all
If I choose each one separately I get a result - if I choose select all I get no result.
This worked fine attaching to another datasource - I have now attached it to a new source.
Tried altering the relationship to properties to a left outer join but this made no difference.
Can someone tell me what else I should check?
thanks
Can an INSERT statement also return all columns inserted as a result set? If so what clause of the INSERT statement does this?
View 3 Replies View Related