Search Results

Search found 37012 results on 1481 pages for 'sql query'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 194/1481 | < Previous Page | 190 191 192 193 194 195 196 197 198 199 200 201  | Next Page >

  • What is your best-practice advice on implementing SQL stored procedures (in a C# winforms applicatio

    - by JYelton
    I have read these very good questions on SO about SQL stored procedures: When should you use stored procedures? and Are Stored Procedures more efficient, in general, than inline statements on modern RDBMS’s? I am a beginner on integrating .NET/SQL though I have used basic SQL functionality for more than a decade in other environments. It's time to advance with regards to organization and deployment. I am using .NET C# 3.5, Visual Studio 2008 and SQL Server 2008; though this question can be regarded as language- and database- agnostic, meaning that it could easily apply to other environments that use stored procedures and a relational database. Given that I have an application with inline SQL queries, and I am interested in converting to stored procedures for organizational and performance purposes, what are your recommendations for doing so? Here are some additional questions in my mind related to this subject that may help shape the answers: Should I create the stored procedures in SQL using SQL Management Studio and simply re-create the database when it is installed for a client? Am I better off creating all of the stored procedures in my application, inside of a database initialization method? It seems logical to assume that creating stored procedures must follow the creation of tables in a new installation. My database initialization method creates new tables and inserts some default data. My plan is to create stored procedures following that step, but I am beginning to think there might be a better way to set up a database from scratch (such as in the installer of the program). Thoughts on this are appreciated. I have a variety of queries throughout the application. Some queries are incredibly simple (SELECT id FROM table) and others are extremely long and complex, performing several joins and accepting approximately 80 parameters. Should I replace all queries with stored procedures, or only those that might benefit from doing so? Finally, as this topic obviously requires some research and education, can you recommend an article, book, or tutorial that covers the nuances of using stored procedures instead of direct statements?

    Read the article

  • How to "escape" a whole String - an SQL query

    - by Ankur
    When I add a relative path to an SQL query as a String all the \ get removed. I am trying to add the String ("pics\\"+onlyFile) and as you can see I have escaped the \ character so I don't understand why it is being removed. onlyFile is a variable containing a file name. The value of the "src" variable (the one I am discussing) just before the SQL statement is correct, hence it is something happening when it is added to the SQL query.

    Read the article

  • Hibernate - query caching/second level cache does not work by value object containing subitems

    - by Zoltan Hamori
    Hi! I have been struggling with the following problem: I have a value object containing different panels. Each panel has a list of fields. Mapping: <class name="com.aviseurope.core.application.RACountryPanels" table="CTRY" schema="DBDEV1A" where="PEARL_CTRY='Y'" lazy="join"> <cache usage="read-only"/> <id name="ctryCode"> <column name="CTRY_CD_ID" sql-type="VARCHAR2(2)" not-null="true"/> </id> <bag name="panelPE" table="RA_COUNTRY_MAPPING" fetch="join" where="MANDATORY_FLAG!='N'"> <key column="COUNTRY_LOCATION_ID"/> <many-to-many class="com.aviseurope.core.application.RAFieldVO" column="RA_FIELD_MID" where="PANEL_ID='PE'"/> </bag> </class> I use the following criteria to get the value object: Session m_Session = HibernateUtil.currentSession(); m_Criteria = m_Session.createCriteria(RACountryPanels.class); m_Criteria.add(Expression.eq("ctryCode", p_Country)); m_Criteria.setCacheable(true); As I see the query cache contains only the main select like select * from CTRY where ctry_cd_id=? Both RACountryPanels and RAFieldVO are second level cached. If I check the 2nd level cache content I can see that it cointains the RAFields and the RACountryPanels as well and I can see the select .. from CTRY where ctry_cd_id=... in query cache region as well. When I call the servlet it seems that it is using the cache, but second time not. If I check the content of the cache using JMX, everything seems to be ok, but when I measure the object access time, it seems that it does not always use the cache. Cheers Zoltan

    Read the article

  • LDAP Query with sub result

    - by StefanE
    I have been banging my head for quite a while with this and can't get it to work. I have a LDAP Query I do have working in AD Users and Computers but dont know how to do it programatically in C#. Here are my LDAP Query that works fine in the AD Tool: (memberOf=CN=AccRght,OU=Groups,OU=P,OU=Server,DC=mydomain,DC=com)(objectCategory=user)(objectClass=user)(l=City) I have used this code to get the user accounts to get members of CN=AccRght but I'm not succeeding on limiting users belonging to a specific city. public StringCollection GetGroupMembers(string strDomain, string strGroup) { StringCollection groupMemebers = new StringCollection(); try { DirectoryEntry ent = new DirectoryEntry("LDAP://DC=" + strDomain + ",DC=com"); DirectorySearcher srch = new DirectorySearcher("(CN=" + strGroup + ")"); SearchResultCollection coll = srch.FindAll(); foreach (SearchResult rs in coll) { ResultPropertyCollection resultPropColl = rs.Properties; foreach( Object memberColl in resultPropColl["member"]) { DirectoryEntry gpMemberEntry = new DirectoryEntry("LDAP://" + memberColl); System.DirectoryServices.PropertyCollection userProps = gpMemberEntry.Properties; object obVal = userProps["sAMAccountName"].Value; if (null != obVal) { groupMemebers.Add(obVal.ToString()); } } } } catch (Exception ex) { Console.Write(ex.Message); } return groupMemebers; } Thanks for any help!

    Read the article

  • showing null rows using join

    - by Pradyut Bhattacharya
    Hi, In mysql i m selecting from a table shouts having a foreign key to another table named "roleuser" with the matching column as user_id Now the user_id column in the shouts table for some rows is null (not actually null but with no inserts in mysql) How to show all the rows of the shouts table either with user_id null or not I m executing the sql statement SELECT s.*, r.firstname, r.lastname FROM shouts s left join roleuser r where r.user_id = s.user_id limit 50; which does not executes and shows You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where r.user_id = s.user_id limit 50' at line 2 but using inner join the sql executes which shows rows which only have user_id values in the shouts table. the nulls are not shown. SELECT s.*, r.firstname, r.lastname FROM shouts s inner join roleuser r where r.user_id = s.user_id limit 50; How can i show all the rows from the shouts table and null values in the firstname and lastname columns where the user_id is null in the shouts table. If not at all possible with sql may be using stored procedures... Thanks Pradyut

    Read the article

  • How to implement paging in NHibernate with a left join query

    - by Gabe Moothart
    I have an NHibernate query that looks like this: var query = Session.CreateQuery(@" select o from Order o left join o.Products p where (o.CompanyId = :companyId) AND (p.Status = :processing) order by o.UpdatedOn desc") .SetParameter("companyId", companyId) .SetParameter("processing", Status.Processing) .SetResultTransformer(Transformers.DistinctRootEntity); var data = query.List<Order>(); I want to implement paging for this query, so I only return x rows instead of the entire result set. I know about SetMaxResults() and SetFirstResult(), but because of the left join and DistinctRootEntity, that could return less than x Orders. I tried "select distinct o" as well, but the sql that is generated for that (using the sqlserver 2008 dialect) seems to ignore the distinct for pages after the first one (I think this is the problem). What is the best way to accomplish this?

    Read the article

  • Re-using aggregate level formulas in SQL - any good tactics?

    - by Cade Roux
    Imagine this case, but with a lot more component buckets and a lot more intermediates and outputs. Many of the intermediates are calculated at the detail level, but a few things are calculated at the aggregate level: DECLARE @Profitability AS TABLE ( Cust INT NOT NULL ,Category VARCHAR(10) NOT NULL ,Income DECIMAL(10, 2) NOT NULL ,Expense DECIMAL(10, 2) NOT NULL ) ; INSERT INTO @Profitability VALUES ( 1, 'Software', 100, 50 ) ; INSERT INTO @Profitability VALUES ( 2, 'Software', 100, 20 ) ; INSERT INTO @Profitability VALUES ( 3, 'Software', 100, 60 ) ; INSERT INTO @Profitability VALUES ( 4, 'Software', 500, 400 ) ; INSERT INTO @Profitability VALUES ( 5, 'Hardware', 1000, 550 ) ; INSERT INTO @Profitability VALUES ( 6, 'Hardware', 1000, 250 ) ; INSERT INTO @Profitability VALUES ( 7, 'Hardware', 1000, 700 ) ; INSERT INTO @Profitability VALUES ( 8, 'Hardware', 5000, 4500 ) ; SELECT Cust ,Profit = SUM(Income - Expense) ,Margin = SUM(Income - Expense) / SUM(Income) FROM @Profitability GROUP BY Cust SELECT Category ,Profit = SUM(Income - Expense) ,Margin = SUM(Income - Expense) / SUM(Income) FROM @Profitability GROUP BY Category SELECT Profit = SUM(Income - Expense) ,Margin = SUM(Income - Expense) / SUM(Income) FROM @Profitability Notice how the same formulae have to be used at the different aggregation levels. This results in code duplication. I have thought of using UDFs (either scalar or table valued with an OUTER APPLY, since many of the final results may share intermediates which have to be calculated at the aggregate level), but in my experience the scalar and multi-statement table-valued UDFs perform very poorly. Also thought about using more dynamic SQL and applying the formulas by name, basically. Any other tricks, techniques or tactics to keeping these kinds of formulae which need to be applied at different levels in sync and/or organized?

    Read the article

  • Impact of ordering of correlated subqueries within a projection

    - by Michael Petito
    I'm noticing something a bit unexpected with how SQL Server (SQL Server 2008 in this case) treats correlated subqueries within a select statement. My assumption was that a query plan should not be affected by the mere order in which subqueries (or columns, for that matter) are written within the projection clause of the select statement. However, this does not appear to be the case. Consider the following two queries, which are identical except for the ordering of the subqueries within the CTE: --query 1: subquery for Color is second WITH vw AS ( SELECT p.[ID], (SELECT TOP(1) [FirstName] FROM [Preference] WHERE p.ID = ID AND [FirstName] IS NOT NULL ORDER BY [LastModified] DESC) [FirstName], (SELECT TOP(1) [Color] FROM [Preference] WHERE p.ID = ID AND [Color] IS NOT NULL ORDER BY [LastModified] DESC) [Color] FROM Person p ) SELECT ID, Color, FirstName FROM vw WHERE Color = 'Gray'; --query 2: subquery for Color is first WITH vw AS ( SELECT p.[ID], (SELECT TOP(1) [Color] FROM [Preference] WHERE p.ID = ID AND [Color] IS NOT NULL ORDER BY [LastModified] DESC) [Color], (SELECT TOP(1) [FirstName] FROM [Preference] WHERE p.ID = ID AND [FirstName] IS NOT NULL ORDER BY [LastModified] DESC) [FirstName] FROM Person p ) SELECT ID, Color, FirstName FROM vw WHERE Color = 'Gray'; If you look at the two query plans, you'll see that an outer join is used for each subquery and that the order of the joins is the same as the order the subqueries are written. There is a filter applied to the result of the outer join for color, to filter out rows where the color is not 'Gray'. (It's odd to me that SQL would use an outer join for the color subquery since I have a non-null constraint on the result of the color subquery, but OK.) Most of the rows are removed by the color filter. The result is that query 2 is significantly cheaper than query 1 because fewer rows are involved with the second join. All reasons for constructing such a statement aside, is this an expected behavior? Shouldn't SQL server opt to move the filter as early as possible in the query plan, regardless of the order the subqueries are written?

    Read the article

  • How do you concat multiple rows into one column in SQL Server?

    - by Jason
    I've searched high and low for the answer to this, but I can't figure it out. I'm relatively new to SQL Server and don't quite have the syntax down yet. I have this datastructure (simplified): Table "Users" | Table "Tags": UserID UserName | TagID UserID PhotoID 1 Bob | 1 1 1 2 Bill | 2 2 1 3 Jane | 3 3 1 4 Sam | 4 2 2 ----------------------------------------------------- Table "Photos": | Table "Albums": PhotoID UserID AlbumID | AlbumID UserID 1 1 1 | 1 1 2 1 1 | 2 3 3 1 1 | 3 2 4 3 2 | 5 3 2 | I'm looking for a way to get the all the photo info (easy) plus all the tags for that photo concatenated like CONCAT(username, ', ') AS Tags of course with the last comma removed. I'm having a bear of a time trying to do this. I've tried the method in this article but I get an error when I try to run the query saying that I can't use DECLARE statements... do you guys have any idea how this can be done? I'm using VS08 and whatever DB is installed in it (I normally use MySQL so I don't know what flavor of DB this really is... it's an .mdf file?)

    Read the article

  • Running a SQL query in rails

    - by Bharat
    Hello I have a rails application where I have to subtract 2 time stamps and display the result on the webpage I have used the following sql statement and I am getting the results. select (julianday(resolutions.created_at)-julianday(tickets.created_at))*24 from tickets,resolutions the value of tickets.created_at is 2010-04-05 18:59:02 which is a time stamp and value of resolutions.created_at is 2010-04-08 08:10:33 Now where do I put this sql so that I can be seen in my webpage. I tired it in the views page with the following but nothing showed up: <% @sql = "select (julianday(resolutions.created_at)-julianday(tickets.created_at))*24 from tickets,resolutions" %> <% @t=(ActiveRecord::Base.connection.execute(@sql)) %> So how do I display it on my webpage? when I perform the above I get the output printed on the webpage as 061.1919444389641(julianday(resolutions.created_at)-julianday(tickets.created_at))*2461.1919444389641 only 61.1919444389641 is supposed to be printed but the query statement is also getting printed.

    Read the article

  • SQL query using information from 4 tables (not all directly linked)

    - by Yvonne
    I'm developing a simple classroom system, where teachers manage classes and their subjects. I have 2 levels of access in my teachers table, assigned by an integer (1 = admin, 2 = user)... Meaning that the headteacher is the admin :) A teacher (of level 1) can have have many classes and a class can have many teachers (so I have 'TeachersClasses' table). A class can have many subjects, and a teacher can have many subjects. Basically, I'm attempting a query to display the admin teacher's (level 1) subjects. However, only teachers with a level of 2, are directly related to a subject, which is set by the admin user. The headteacher can view all of their subjects via the classroom, but I cannot get all of the subjects to be displayed on one page, instead I can only get the subjects to appear under a specific classroom right now... This is what I have so far, which is returning nothing. (I'm guessing this may require an SQL clause more advanced that 'INNER JOIN' which is the only join type I am familiar with, and thought it would be enough! $query = "SELECT subjects.subjectid, subjects.subjectname, subjects.subjectdetails, classroom.classid, classroom.classname FROM subjects INNER JOIN classroom ON subjects.subjectid = classroom.classid INNER JOIN teacherclasses ON classroom.classid = teacherclasses.classid INNER JOIN teachers ON teacherclasses.teacherid = teachers.teacherid WHERE teachers.teacherid = '".intval( $_SESSION['SESS_TEACHERID'] )."'"; In order for all subjects related to the headteachers class to be displayed, I'm gathering that all of my tables will need to be called up here? Thanks for any help! Example output: subject name: maths // teacher: mr smith // classroom: DG99 x10 for all the subjects associated with the headteachers classrooms :)

    Read the article

  • Why does a conditional not affect query speed?

    - by Telos
    I have a stored procedure that was taking a "long" period of time to execute. The query only needs to return data in one case, so I figured I could check for that case and just return before hitting the actual query. The only problem is that it still takes the same amount of time to execute with an if statement. I have verified that the code inside the if is not executing, and that if I replace the complex query with a simple select the speed is fine... so now I'm confused. Why is the query being slowed down by code that doesn't get executed when the conditional is false? Here's the query itself: ALTER PROCEDURE [dbo].[pr_cbc_GetCokeInfo] @pa_record int, @pb_record int AS BEGIN SET NOCOUNT ON; declare @ticketRec int SELECT @ticketRec = TicketRecord FROM eservice_live..v_sdticket where TicketRecord=@pa_record AND serviceCompanyID = 1139 AND @pb_record IS NULL if @ticketRec IS NULL return select record = null, doc_ref = @pa_record, memo_type = 'I', memo = 'Bottler: ' + isnull(Bottler, '') + ' ' + 'Sales Loc: ' + isnull(SalesLocation, '') + ' ' + 'Outlet Desc: ' + isnull(OutletDesc, '') + ' ' + 'City: ' + isnull(OutletCity, '') + ' ' + 'EquipNo: ' + isnull(EquipNo, '') + ' ' + 'SerialNo: ' + isnull(SerialNo, '') + ' ' + 'PhaseNo: ' + isnull(cast(PhaseNo as varchar(255)), '') + ' ' + 'StaticIP: ' + isnull(StaticIP, '') + ' ' + 'Air Card: ' + isnull(AirCard, '') FROM eservice_live..v_SDExtendedInfoField ef JOIN eservice_live..CokeSNList csl ON ef.valueText=csl.SerialNo where ef.docType='CLH' AND ef.docref = @ticketRec AND ef.ExtendedDocNumber=5 SET NOCOUNT OFF; END

    Read the article

  • SQL Server insert performance

    - by Jose
    I have an insert query that gets generated like this INSERT INTO InvoiceDetail (LegacyId,InvoiceId,DetailTypeId,Fee,FeeTax,Investigatorid,SalespersonId,CreateDate,CreatedById,IsChargeBack,Expense,RepoAgentId,PayeeName,ExpensePaymentId,AdjustDetailId) VALUES(1,1,2,1500.0000,0.0000,163,1002,'11/30/2001 12:00:00 AM',1116,0,550.0000,850,NULL,@ExpensePay1,NULL); DECLARE @InvDetail1 INT; SET @InvDetail1 = (SELECT @@IDENTITY); This query is generated for only 110K rows. It takes 30 minutes for all of these query's to execute I checked the query plan and the largest % nodes are A Clustered Index Insert at 57% query cost which has a long xml that I don't want to post. A Table Spool which is 38% query cost <RelOp AvgRowSize="35" EstimateCPU="5.01038E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Eager Spool" NodeId="80" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="0.0466109"> <OutputList> <ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvoiceId" /> <ColumnReference Database="[SkipPro]" Schema="[dbo]" Table="[InvoiceDetail]" Column="InvestigatorId" /> <ColumnReference Column="Expr1054" /> <ColumnReference Column="Expr1055" /> </OutputList> <Spool PrimaryNodeId="3" /> </RelOp> So my question is what is there that I can do to improve the speed of this thing? I already run ALTER TABLE TABLENAME NOCHECK CONSTRAINTS ALL Before the queries and then ALTER TABLE TABLENAME NOCHECK CONSTRAINTS ALL after the queries. And that didn't shave off hardly anything off of the time. Know I am running these queries in a .NET application that uses a SqlCommand object to send the query. I then tried to output the sql commands to a file and then execute it using sqlcmd, but I wasn't getting any updates on how it was doing, so I gave up on that. Any ideas or hints or help?

    Read the article

  • Strange: Planner takes decision with lower cost, but (very) query long runtime

    - by S38
    Facts: PGSQL 8.4.2, Linux I make use of table inheritance Each Table contains 3 million rows Indexes on joining columns are set Table statistics (analyze, vacuum analyze) are up-to-date Only used table is "node" with varios partitioned sub-tables Recursive query (pg = 8.4) Now here is the explained query: WITH RECURSIVE rows AS ( SELECT * FROM ( SELECT r.id, r.set, r.parent, r.masterid FROM d_storage.node_dataset r WHERE masterid = 3533933 ) q UNION ALL SELECT * FROM ( SELECT c.id, c.set, c.parent, r.masterid FROM rows r JOIN a_storage.node c ON c.parent = r.id ) q ) SELECT r.masterid, r.id AS nodeid FROM rows r QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------- CTE Scan on rows r (cost=2742105.92..2862119.94 rows=6000701 width=16) (actual time=0.033..172111.204 rows=4 loops=1) CTE rows -> Recursive Union (cost=0.00..2742105.92 rows=6000701 width=28) (actual time=0.029..172111.183 rows=4 loops=1) -> Index Scan using node_dataset_masterid on node_dataset r (cost=0.00..8.60 rows=1 width=28) (actual time=0.025..0.027 rows=1 loops=1) Index Cond: (masterid = 3533933) -> Hash Join (cost=0.33..262208.33 rows=600070 width=28) (actual time=40628.371..57370.361 rows=1 loops=3) Hash Cond: (c.parent = r.id) -> Append (cost=0.00..211202.04 rows=12001404 width=20) (actual time=0.011..46365.669 rows=12000004 loops=3) -> Seq Scan on node c (cost=0.00..24.00 rows=1400 width=20) (actual time=0.002..0.002 rows=0 loops=3) -> Seq Scan on node_dataset c (cost=0.00..55001.01 rows=3000001 width=20) (actual time=0.007..3426.593 rows=3000001 loops=3) -> Seq Scan on node_stammdaten c (cost=0.00..52059.01 rows=3000001 width=20) (actual time=0.008..9049.189 rows=3000001 loops=3) -> Seq Scan on node_stammdaten_adresse c (cost=0.00..52059.01 rows=3000001 width=20) (actual time=3.455..8381.725 rows=3000001 loops=3) -> Seq Scan on node_testdaten c (cost=0.00..52059.01 rows=3000001 width=20) (actual time=1.810..5259.178 rows=3000001 loops=3) -> Hash (cost=0.20..0.20 rows=10 width=16) (actual time=0.010..0.010 rows=1 loops=3) -> WorkTable Scan on rows r (cost=0.00..0.20 rows=10 width=16) (actual time=0.002..0.004 rows=1 loops=3) Total runtime: 172111.371 ms (16 rows) (END) So far so bad, the planner decides to choose hash joins (good) but no indexes (bad). Now after doing the following: SET enable_hashjoins TO false; The explained query looks like that: QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CTE Scan on rows r (cost=15198247.00..15318261.02 rows=6000701 width=16) (actual time=0.038..49.221 rows=4 loops=1) CTE rows -> Recursive Union (cost=0.00..15198247.00 rows=6000701 width=28) (actual time=0.032..49.201 rows=4 loops=1) -> Index Scan using node_dataset_masterid on node_dataset r (cost=0.00..8.60 rows=1 width=28) (actual time=0.028..0.031 rows=1 loops=1) Index Cond: (masterid = 3533933) -> Nested Loop (cost=0.00..1507822.44 rows=600070 width=28) (actual time=10.384..16.382 rows=1 loops=3) Join Filter: (r.id = c.parent) -> WorkTable Scan on rows r (cost=0.00..0.20 rows=10 width=16) (actual time=0.001..0.003 rows=1 loops=3) -> Append (cost=0.00..113264.67 rows=3001404 width=20) (actual time=8.546..12.268 rows=1 loops=4) -> Seq Scan on node c (cost=0.00..24.00 rows=1400 width=20) (actual time=0.001..0.001 rows=0 loops=4) -> Bitmap Heap Scan on node_dataset c (cost=58213.87..113214.88 rows=3000001 width=20) (actual time=1.906..1.906 rows=0 loops=4) Recheck Cond: (c.parent = r.id) -> Bitmap Index Scan on node_dataset_parent (cost=0.00..57463.87 rows=3000001 width=0) (actual time=1.903..1.903 rows=0 loops=4) Index Cond: (c.parent = r.id) -> Index Scan using node_stammdaten_parent on node_stammdaten c (cost=0.00..8.60 rows=1 width=20) (actual time=3.272..3.273 rows=0 loops=4) Index Cond: (c.parent = r.id) -> Index Scan using node_stammdaten_adresse_parent on node_stammdaten_adresse c (cost=0.00..8.60 rows=1 width=20) (actual time=4.333..4.333 rows=0 loops=4) Index Cond: (c.parent = r.id) -> Index Scan using node_testdaten_parent on node_testdaten c (cost=0.00..8.60 rows=1 width=20) (actual time=2.745..2.746 rows=0 loops=4) Index Cond: (c.parent = r.id) Total runtime: 49.349 ms (21 rows) (END) - incredibly faster, because indexes were used. Notice: Cost of the second query ist somewhat higher than for the first query. So the main question is: Why does the planner make the first decision, instead of the second? Also interesing: Via SET enable_seqscan TO false; i temp. disabled seq scans. Than the planner used indexes and hash joins, and the query still was slow. So the problem seems to be the hash join. Maybe someone can help in this confusing situation? thx, R.

    Read the article

  • return sql query in xml format in python

    - by Ramy
    When I first started working at the company that i work at now, I created a java application that would run batches of jasper-reports. In order to determine which parameters to use for each report in the set of reports, I run a sql query (on sqlserver). I wrote the application to take an xml file with a set of parameters for each report to be generated in the set. so, my process has become, effectively, three steps: run the sql query and return the results in XML format (using 'for XML auto') run the results of the sql query through an XSLT transformation so the xml is formatted in such a way that is friendly with the java application i wrote. run the java application with that final xml file As you can imagine, what I'd like to do is accomplish these steps in python, but i'm not quite sure how to get started. I know how to run an SQL query in Python. I see plenty of documentation about how to write your own xml document with Python. I even see documentation for xsl transformations in python. the big question is how to get the results of the sql query in XML through python. Any and all pointers would be very valuable. Thanks, _Ramy

    Read the article

  • why cant I more than two values from 3 different tables in one query

    - by zurna
    This is strange. In the news details page, I want to take a few different values from different tables with one query. However, for some strange reason, I only get two values back. So the outcome is like: <rows> <row id="4"> <FullName>Efe Tuncel</FullName> <CategoryName/> <Title>Runway Report</Title> <ShortDesc/> <Desc></Desc> <Date/> </row> </rows> If I disable fullname, then I get shortdesc but not others. Same things happens with others. NewsID = Request.QueryString("NEWSID") SQL = "SELECT N.NewsID, N.MembersID, N.CategoriesID, N.ImagesID, N.NewsTitle, N.NewsShortDesc, N.NewsDesc, N.NewsActive, N.NewsDateEntered, C.CategoriesID, C.CategoriesName, M.MembersID, M.MembersFullName" SQL = SQL & " FROM News N, Categories C, Members M" SQL = SQL & " WHERE N.NewsID = "& NewsID &" AND N.NewsActive = 1 AND N.MembersID = M.MembersID AND N.CategoriesID = C.CategoriesID" Set objViewNews = objConn.Execute(SQL) With Response .Write "<?xml version='1.0' encoding='windows-1254' ?>" .Write "<rows>" End With With Response .Write "<row id='"& objViewNews("NewsID") &"'>" .Write "<FullName>"& objViewNews("MembersFullName") &"</FullName>" .Write "<CategoryName>"& objViewNews("CategoriesName") &"</CategoryName>" .Write "<Title>"& objViewNews("NewsTitle") &"</Title>" .Write "<ShortDesc>"& objViewNews("NewsShortDesc") &"</ShortDesc>" .Write "<Desc><![CDATA["& objViewNews("NewsDesc") &"]]></Desc>" .Write "<Date>"& objViewNews("NewsDateEntered") &"</Date>" .Write "</row>" End With With Response .Write "</rows>" End With objViewNews.Close Set objViewNews = Nothing

    Read the article

  • broken SQL 2008 SP1 Web Edition (can not login with SSMS)

    - by gerryLowry
    Scenario: My installation of SQL Server 2008 Web Edition SP1 was working properly. Since I've recently joined Microsoft's Website Spark*, I removed SQL2008 and installed SQL 2008 again using my Website Spark edition and license from the MSDN download site. Next, I updated SQL 2008 to SP1 (this is required because I'm running Windows 2008 Server R2 Web edition). When I launch SSMS (SQL Server Management Studio), "User name" is "myhost\Administrator" and is greyed out so it can not be changed. When I installed my Website Spark version, I did not include "myhost\Administrator" when I was configuring SQL 2008 service accounts. Instead I created an administrator account "myhost\mySQLaccount". ERROR MESSAGE: Connect to Server (X) Cannot connect to (local) Additional information: Login failed for user 'myhost'Admistrator' (Microsoft SQL Server, Error: 18456) I tried to use the SQL Server Configuration Manager to correct this problem but could not find any useful way to fix this issue. How to I fix this problem? Connect to Server ... Server type: Database Engine Server name: (local) Authentication: Windows Authentication Please advise. Thank you. Gerry * http://www.microsoft.com/web/websitespark/default.aspx

    Read the article

  • Can I store SQL Server sort order in a variable?

    - by Steve Weet
    I have the following SQL within a stored procedure. Is there a way to remove the IF statement and pass the 'ASC'/'DESC' option as a variable? I know I could do the query a number of different ways, or return a table and sort it externally etc. I would just like to know if I can avoid duplicating the CASE statement. IF @sortOrder = 'Desc' BEGIN SELECT * FROM #t_results ORDER BY CASE WHEN @OrderBy = 'surname' THEN surname END DESC, CASE WHEN @OrderBy = 'forename' THEN forename END DESC, CASE WHEN @OrderBy = 'fullName' THEN fullName END DESC, CASE WHEN @OrderBy = 'userId' THEN userId END DESC, CASE WHEN @OrderBy = 'MobileNumber' THEN MSISDN END DESC, CASE WHEN @OrderBy = 'DeviceStatus' THEN DeviceStatus END DESC, CASE WHEN @OrderBy = 'LastPosition' THEN LastPosition END DESC, CASE WHEN @OrderBy = 'LastAlert' THEN LastAlert END DESC, CASE WHEN @OrderBy = 'LastCommunication' THEN LastCommunication END DESC, CASE WHEN @OrderBy = 'LastPreAlert' THEN LastPreAlert END DESC END ELSE BEGIN SELECT * FROM #t_results ORDER BY CASE WHEN @OrderBy = 'surname' THEN surname END DESC, CASE WHEN @OrderBy = 'forename' THEN forename END DESC, CASE WHEN @OrderBy = 'fullName' THEN fullName END DESC, CASE WHEN @OrderBy = 'userId' THEN userId END DESC, CASE WHEN @OrderBy = 'MobileNumber' THEN MSISDN END DESC, CASE WHEN @OrderBy = 'DeviceStatus' THEN DeviceStatus END DESC, CASE WHEN @OrderBy = 'LastPosition' THEN LastPosition END DESC, CASE WHEN @OrderBy = 'LastAlert' THEN LastAlert END DESC, CASE WHEN @OrderBy = 'LastCommunication' THEN LastCommunication END DESC, CASE WHEN @OrderBy = 'LastPreAlert' THEN LastPreAlert END DESC END END

    Read the article

  • oracle query with inconsistent results

    - by Spencer Stejskal
    Im having a very strange problem, i have a complicated view that returns incorrect data when i query on a particular column. heres an example: select empname, has_garnishment from timecard_v2 where empname = 'Testerson, Testy'; this returns the single result 'Testerson, Testy', 'N' however, if i use the query: select empname, has_garnishment from timecard_v2 where empname = 'Testerson, Testy' and has_garnishment = 'Y'; this returns the single result 'Testerson, Testy', 'Y' The second query should return a subset of the first query, but it returns a different answer. I have dissected the view and determined that this section of the view definition is where the problem arises(Note, I removed all of the select clause except the parts of interests for clarity, in the full query all joined tables are required): SELECT e.fullname empname , NVL2(ded.has_garn, 'Y', 'N') has_garnishment FROM timecard tc , orderdetail od , orderassign oa , employee e , employee3 e3 , customer10 c10 , order_misc om, (SELECT COUNT(*) has_garn, v_ssn FROM deductions WHERE yymmdd_stop = 0 OR (LENGTH(yymmdd_stop) = 7 AND to_date(SUBSTR(yymmdd_stop, 2), 'YYMMDD') sysdate) GROUP BY v_ssn ) ded WHERE oa.lrn(+) = tc.lrn_order AND om.lrn(+) = od.lrn AND od.orderno = oa.orderno AND e.ssn = tc.ssn AND c10.custno = tc.custno AND e.lrn = e3.lrn AND e.ssn = ded.v_ssn(+) One thing of note about the definition of the 'ded' subquery. The v_ssn field is a virtual field on the deductions table. I am not a DBA im a software developer but we recently lost our DBA and the new one is still getting up to speed so im trying to debug this issue. That being said, please explain things a little more thoroughly then you would for a fellow oracle expert. thanks

    Read the article

  • Paramaterising SQL in SSIS

    - by Anonymouslemming
    Hi all, I'm trying to paramaterize some queries in SSIS. After some reading, it sounds like my best option is to create one variable that contains my base sql, another that contains my criteria and a final variable that is evaluated as an expression that includes both of these. I want to end up with an SQL query that is effectively UPDATE mytable set something='bar' where something_else='foo' So my first two variables have the scope of my package and are as follows: Name: BaseSQL Data Type: String Value: UPDATE mytable set something = 'bar' where something_else = Name: MyVariable Data Type: String Value: foo My third variable has a scope of the data flow task where I want to use this SQL and is as follows: Name: SQLQuery Data Type: String Value: @[User::BaseSQL] + "'" + @[User::MyVariable] + "'" EvaluateAsExpression: True In the OLE DB Source, I then choose my connection and 'SQL command from variable' and select User::SQLQuery from the dropdown box. The Variable Value window then displays the following: @[User::BaseSQL] + "'" + @[User::MyVariable] + "'" This is as desired, and would provide the output I want from my DB. The variable name dropdown also contains User::BaseSQL and User::MyVariable so I believe that my namespaces are correct. However, when I then click preview, I get the following error when configuring an OLE DB Source (using SQL command from variable): TITLE: Microsoft Visual Studio Error at Set runtime in DB [Set runtime in myDb DB [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Statement(s) could not be prepared.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Must declare the scalar variable "@".". (Microsoft Visual Studio) Can anyone advise what I'm missing or how I can resolve this please ? Thanks in advance!

    Read the article

  • deleting records from multiple tables at a time with a single query in sqlserver2005

    - by sudhavamsikiran
    Hi I wanna delete records from child tables as well as parent table with in a single query. please find the query given below. here response header is the primary table and responseid is the primary key. DELETE FROM responseheader FROM responseheader INNER JOIN responsepromotion ON responseheader.responseid = responsepromotion.ResponseID INNER JOIN responseext ON responsepromotion.ResponseID=responseext.ResponseID WHERE responseheader.responseid In ('67D8B9E8-BAD2-42E6-BAEA-000025D56253') but its throwing error . can any one help me to find out the correct query

    Read the article

  • Avoiding secondary selects or joins with Hibernate Criteria or HQL query

    - by Ben Benson
    I am having trouble optimizing Hibernate queries to avoid performing joins or secondary selects. When a Hibernate query is performed (criteria or hql), such as the following: return getSession().createQuery(("from GiftCard as card where card.recipientNotificationRequested=1").list(); ... and the where clause examines properties that do not require any joins with other tables... but Hibernate still performs a full join with other tables (or secondary selects depending on how I set the fetchMode). The object in question (GiftCard) has a couple ManyToOne associations that I would prefer to be lazily loaded in this case (but not necessarily all cases). I want a solution that I can control what is lazily loaded when I perform the query. Here's what the GiftCard Entity looks like: @Entity @Table(name = "giftCards") public class GiftCard implements Serializable { private static final long serialVersionUID = 1L; private String id_; private User buyer_; private boolean isRecipientNotificationRequested_; @Id public String getId() { return this.id_; } public void setId(String id) { this.id_ = id; } @ManyToOne @JoinColumn(name = "buyerUserId") @NotFound(action = NotFoundAction.IGNORE) public User getBuyer() { return this.buyer_; } public void setBuyer(User buyer) { this.buyer_ = buyer; } @Column(name="isRecipientNotificationRequested", nullable=false, columnDefinition="tinyint") public boolean isRecipientNotificationRequested() { return this.isRecipientNotificationRequested_; } public void setRecipientNotificationRequested(boolean isRecipientNotificationRequested) { this.isRecipientNotificationRequested_ = isRecipientNotificationRequested; } }

    Read the article

  • How to have a where clause on an insert or an update in Linq to Sql?

    - by Kelsey
    I am trying to convert the following stored proc to a LinqToSql call (this is a simplied version of the SQL): INSERT INTO [MyTable] ([Name], [Value]) SELECT @name, @value WHERE NOT EXISTS(SELECT [Value] FROM [MyTable] WHERE [Value] = @value) The DB does not have a constraint on the field that is getting checked for so in this specific case the check needs to be made manually. Also there are many items constantly being inserted as well so I need to make sure that when this specific insert happens there is no dupe of the value field. My first hunch is to do the following: using (TransactionScope scope = new TransactionScope()) { if (Context.MyTables.SingleOrDefault(t => t.Value == in.Value) != null) { MyLinqModels.MyTable t = new MyLinqModels.MyTable() { Name = in.Name, Value = in.Value }; // Do some stuff in the transaction scope.Complete(); } } This is the first time I have really run into this scenario so I want to make sure I am going about it the right way. Does this seem correct or can anyone suggest a better way of going about it without having two seperate calls? Edit: I am running into a similar issue with an update: UPDATE [AnotherTable] SET [Code] = @code WHERE [ID] = @id AND [Code] IS NULL How would I do the same check with Linqtosql? I assume I need to do a get and then set all the values and submit but what if someone updates [Code] to something other than null from the time I do the get to when the update executes? Same problem as the insert...

    Read the article

  • Nhibernate Exception - Return types of SQL query were not specified

    - by Muhammad Akhtar
    I am executing SQL in hibernate and getting exception Return types of SQL query were not specified public ArrayList get(string Release, int DocId) { string query = string.Format("select ti.Id, (' Defect ' + cast(ti.onTimeId as varchar) + ' - ' + ti.Name) as Name from TrackingItems ti inner join DocumentTrackingItems dti on ti.Id = dti.ItemStepId inner join Documents doc on dti.DocumentId = doc.Id where ti.ReleaseId = '{0}' AND doc.TypeId = {1} and Doc.Name is null AND ti.Type = 'Defect'", Release, DocId); ISession session = NHibernateHelper.GetCurrentSession(); ArrayList arList = (ArrayList)session.CreateSQLQuery(query).List(); return arList; } When I run this query in SQL, it working fine. any idea what could be the issue? -------- Thanks.........

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 190 191 192 193 194 195 196 197 198 199 200 201  | Next Page >