select inside update query?

Getting started with SQL Server

Index ‹ SQL Server

- Previous
  - 1
    - job failed om the sql server Which service account is the service account running with Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: select inside update query?
  - 2
    - Cannot shut down VS after using report designer Same bug here, but when a Infragistics control (Windocker) breaks at design time. Grrrrr... There is an open bug track on this at: http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx feedbackid=c6519d93-581a-4eea-85f5-143e9d790d4e Tag: select inside update query?
  - 3
    - Report Header woes Um, folks... hold on a sec . I saw the re-post on this question and I think I have succeeded in doing what you want. (Sorry I didn't get around to posting this last night.) It's a little hacky and it probably won't work for *every* situation, but I did get the basic parameters down: * -- I've got a Body section with two columns * -- I have a header that appears to stretch over both areas * -- it "collapses" after the first page. Here is what I did: I couldn't get it to work as a Page header, for reasons you've already discussed. So, instead, I worked out how you could have a Body header that disappeared properly and stretched across. The first step is to figure out where to put it. In my example, and I am not sure how many other ways there might be to do it, it's a group header and I page breaked on group. I don't think this is relevant, but whatever your data region you need a body element that will be placed at the top of every column. The background of this page header element should be transparent, because we're going to change its contents for the second column (or more, if it's > 2 cols) and we want it to look as seamless -across as possible. For the same reason (making the header look seamless-across), you probably want to set the colum spacing as low as possible (you can add some extra padding on the right side by adding an extra blank column, and merge the layout element across, to compensate). Note that, just as we'll make the contents different for each col, text alignment can be an expression, so using what I"m doing here you could (say) have the left col header left aligned and the right col header right aligned. I set the expression for this header element to =Code.MyUDF() . You can see my code below. Notice that the ColNo var is incremented each time, so I can use it both for "which column am I in" and "which page am I on". The tricky part was just figuring out when the Visibility of the element would be evaluated with respect to this function being invoked -- so that I could set the visibility properly. With how I'm doing it here, the visibility expression has to be =Code.ColNo > 1 (one less than you might think, IOW). The visibility expression would be a little different if I invoked one function to increment earlier, say in an earlier no-height line, and a second function to figure out the expressions to show, but this combined design works okay as well. I also set the CanShrink property of this element to True to and I set the Visibility of the row to the same expression as the layout element. Here's the code I used: Code Snippet Public ColNo AS Integer = 0 Function MyUDF AS String Dim myheadpart As String ColNo = ColNo + 1 If ColNo = 1 myheadpart= "1st part of header" ElseIf ColNo = 2 myheadpart= "2nd part" Else myheadpart = "" End If Return myheadpart End Function ... HTH, >L< Tag: select inside update query?
  - 4
    - Dynamic SQL and sp_executesql How do you mean pass both The searchword can change for each search since it is an search in a application, so i have no clue if the user writes word1 OR word2 .. or maybe word2 AND word5 OR word6 Tag: select inside update query?
  - 5
    - Service Broker "TO service could not be found" Message origin: "Transport" OK, well I'm not sure if this is promising or not, but now my trace shows: This message could not be delivered because the Service Contract could not be found. Service Contract: 'tcp://mydomain.com/INITATE/MyMessage/v1.0. I see this contract defined on both the initiator and target machines. Tag: select inside update query?
  - 6
    - Where clause in a SET Thanks. I see I chose a confusing example. I am using "Sales" in the fact table to mean dollar amount and "Sales" in the dimenstion table to mean type of Sale. I changed that termanology. I am already using a Year/Month parameter to pare down the result set. Perhaps it would be easier to make SalesType a parameter and set it to "Sales" and let SSAS handle it. When I do that (and let SSRS build most of my query for me) I get the following query, which works pretty good: WITH SET [Ordered Customers] as ORDER ( NONEMPTY ([Dim Customer].[Name].[Name]. members ,[Measures].[Amount]), [Measures].[Amount], BDESC ) MEMBER measures.[Sales Rank] AS RANK ([Dim Customer].[Name]. CurrentMember ,[Ordered Customers]) SELECT NON EMPTY { [Measures].[Gross Profit], [Measures].[Coil Weight], [Measures].[Steel Margin], [Measures].[Gross Margin], [Measures].[Amount], [Measures].[Sales Rank] } ON COLUMNS , NON EMPTY { ([Dim Customer].[Account Code].[Account Code]. ALLMEMBERS * [Dim Customer].[Name].[Name]. ALLMEMBERS * [Date Shipped].[Date].[Date]. ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION , MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET (@DimSalesTypeSalesType, CONSTRAINED ) ) ON COLUMNS FROM ( SELECT ( STRTOSET (@DateShippedYearMonth, CONSTRAINED ) ) ON COLUMNS FROM [Heidtman DW])) WHERE ( IIF ( STRTOSET (@DateShippedYearMonth, CONSTRAINED ). Count = 1, STRTOSET (@DateShippedYearMonth, CONSTRAINED ), [Date Shipped].[Year-Month]. currentmember ), IIF ( STRTOSET (@DimSalesTypeSalesType, CONSTRAINED ). Count = 1, STRTOSET (@DimSalesTypeSalesType, CONSTRAINED ), [Dim Sales Type].[Sales Type]. currentmember ) ) CELL PROPERTIES VALUE , BACK_COLOR , FORE_COLOR , FORMATTED_VALUE , FORMAT_STRING , FONT_NAME , FONT_SIZE , FONT_FLAGS My only problem now is that the cross joins are producing several hundred rows that return null values. It doesn't seem to effect the results, it just causes the report to run very slowly. What I am eventually going to do is add five different rankings to the report. I think it will work but, again, there is going to be a speed issure. Do you see any way of excluding null results Like I said, I let SSRS build most of the query for my and I don't really understand all that it is doing. Tag: select inside update query?
  - 7
    - Can i set somehow the package to have the components automaticaly updated? Help ... i'm lost I have one custom dataflow component in assembly A with version 1.1.0.0 used in many packages. The component is now in assembly A with version 1.2.0.0 but not changed. There is aniway to have those packaged configured in order to automaticaly be upgrated to use the new version of the component I tried (in this order): - set the property "UpdateObjects" to true on package (while version 1.1.0.0 was available in GAC) and saved package. - unistalled the version 1.1.0.0 from GAC. - tried to reopen package with "Reload and Upgrade" option. NO SUCCESS I know that can be an brute way to do it, meaning replace as text in xml ... but i try to find a solution not this stupid "workaround" Many thanx for any suggestions! Catalin PS: Excuse my english. Tag: select inside update query?
  - 8
    - Table transfer errors I noticed another issue... The tables that did succeed in the transfer are without constraints. Is there a switch I have to flip in order to have the Import Export wizard transfer over the constraints Thanks, David Tag: select inside update query?
  - 9
    - Query takes more time from user interface or in a network I execute a pretty big sql query which joins multiple tables and I reviewed indexes on all these tables. I am happy with the result when I run the query using SSMS in the server locally i.e., where my SQL Server database is installed. It takes 4 seconds to get around 17000 records. If I run the same query in a network or from my desktop using SSMS i.e., i connect to the above mentioned SQL Server using SSMS, it takes more than 60 seconds. Not sure how to solve this. If someone could help me, it will be of great help. Thanks. Tag: select inside update query?
  - 10
    - Packege success, despite task failure Hello, When I run my package, a task will fail, however, the package will claim that it was successful. Why is this, and how can I trigger a failed package when one task fails Thanks in advance. Tag: select inside update query?
  - 11
    - Can i host a SQL2005 EE DB on SQL2000 engine ? hi Amol, nope, this is not an option... you can not move a SQL Server 2005 database to a SQL Server 2000 instance even if modifying the compatibility level... regards Tag: select inside update query?
  - 12
    - Incorrect processing order for views I don't konw that merge replication has great dependency checks like tran replication does, but for this scenario, it's not uncommon to separate out the user procs/views/functions/etc into a separate snapshot or tran publication and refresh it periodically (or on a daily schedule) when you know you've made changes. Tag: select inside update query?
  - 13
    - Unable to read local eventlog (the parameter is incorrect) This is fixed in SP1 Beta and is available for download. Thanks, Gops Dwarak Tag: select inside update query?
  - 14
    - Database Blobs & Performance Hi geniuses, I have a core database question in MS Access and SQL Server. Assume the following table in MS Access or SQL Server: Employees table: EmpID, Name, Hiredate, Photo; and the following query: "SELECT EmpID, Name, Hiredate FROM Employees;" The question is, "does the blob object ( Photo) affect the performance of the query even when not included in the select statement " I mean, will I gain some additional performance when separating the blob fields to another table Thanks for the answer. Tag: select inside update query?
  - 15
    - How to extract domain name from an email address?? Yep, that works. Could do it in one step if needed, but it is probably less efficient Code Snippet set @domain = substring(@email, charindex( '@' , @email)+1, (len(@email) - charindex('.', reverse(@email))) - charindex( '@' , @email)) Chris Tag: select inside update query?
  - 16
    - problem connecting Remote Analysis service 2005 from vb.net 2005 Hi Darren, i am not able to connect analysis service from my sql management studio, but i could connect datbase engine using sql server authentication and query the db. when i select server type as analysis service, authentication combo got disabled, couldn't select anything. Tag: select inside update query?
- Next
  - 1
    - SSAS processing The processing commands sent from BIDS and SSMS should be identical so I can't see why it would work from one and not the other. You chould be able to capture the XMLA using profiler and confirm that the same commands are being sent. But the problem is data or dimension /cube structure is not updated. Are you talking about processing or deploying BIDS can do both. SSMS and SSIS only do processing, they do not deploy changes to the cube/dimension structure. Tag: select inside update query?
  - 2
    - Schema (owner) Names Not Visible In Shapes On Diagram When reverse engineering a SQL Server 2005 database (using VS 2007 Professional), I can't figure out a way to display the schema name in front of the table name in the database diagram. For instance, If I have a table named Person.Type, it just comes over as Type. It knows what the schema is because it's in the properties box for that object. It just doesn't appear in the name on the shape itself. Is there a setting somewhere that I'm missing that will display the schema in the name Tag: select inside update query?
  - 3
    - Select Statement Problem? Here It is, Code Snippet Create Table #data ( [name] Varchar ( 100 ) , [telephone1] Varchar ( 100 ) , [telephone2] Varchar ( 100 ) ); Insert Into #data Values ( 'ian' , '1234567' , '7654321' ); Insert Into #data Values ( 'test' , '1111111' , '2222222' ); Code Snippet select case when type = 1 then [name] else '' end [name] , telephone from ( select [name] , 1 [type] , telephone = telephone1 from #data union all select [name] , 2 [type] , telephone2 from #data ) as data order by data . [name] , data . [type] Tag: select inside update query?
  - 4
    - MS SQL2005 express ed + SSIS installation problem Hi, I have tried to install SSIS going steps msdn2 using "mssql setup" but my "ssis" option is not available for installation. Pls, help! Tag: select inside update query?
  - 5
    - Problem in MDX query for calculating total sales. hi, can someone help in finding the error in this mdx query for the given problem statement. Query : Sales performance by gender of the customers. - The expected output is 2 values of sales ,one for all male customers and other for all female customers. - The level CustomerID of dimension Customer has attribute GENDER. - I have calculated two sets [Male] and [Female] . - At present i am getting sales value for all the members ,i want one aggregated value for male set and another value for female set.How do i go about doing this Please help. MDX Query : WITH set [Male] AS 'Filter ([Customer].[CustomerID].Members,[Customer].CurrentMember.Properties("GENDER")= "M")' set [Female] AS 'Filter ([Customer].[CustomerID].Members,[Customer].CurrentMember.Properties("GENDER")= "F")' SELECT {[Measures].[SALES($)]} ON AXIS(0), {[Male],[Female]} ON AXIS(1) FROM [cubename] Tag: select inside update query?
  - 6
    - help enabling FTS in multiple database 1. Is the server which is having the problem have 2 GB RAM 2. How big is the existing FTS catalog for which FTS is working 3. Setup perfmon logging on the server (REF: http://yukonspace.blogspot.com/2007/09/setting-up-perfmon-logs.html ) and monitor the following during the problem period: Available Mbytes on the server (Under MEMORY object) Virtual Bytes for the process (sqlservr.exe(s) and mssearch.exe) (Under PROCESS object) Working Set for the above mentioned processes (Under PROCESS object) 4. What to the FTS gatherer logs report (REF for reading gatherer logs: http://support.microsoft.com/kb/289653 ) 5. Do you get any error message when you run sp_fulltext_database from Query Analyzer Thanks Tag: select inside update query?
  - 7
    - Dervied Column - Expression? Please disregard my first answer; I thought you were asking how to translate the case statement into a SSIS expression. Anyway; have you consider to include that query with the query in your source component; assuming all tables are in the same DB it would work just fine. In case the data is in different DB/Server; you could use a staging table to load the data on a common place and then have one single query. Otherwise a second source component with a merge join may be an option. Tag: select inside update query?
  - 8
    - How Do I Hide Internet Explorer Menu and Tool Bar I tried that , but it did not work since the SQL Reporting Service launches the Internet Explorer. Is there a way to configure the the Report to pass parameters to IE Tag: select inside update query?
  - 9
    - The INSERT statement conflicted with the FOREIGN KEY constraint FK_source_destination. " And then if you get erros on adding data to these tables, it will be because the [UserID] does not exists in the [Users] table." Ah... silly me. Yes, that was it. Thanks again Arnie. Cheers, Peter Tag: select inside update query?
  - 10
    - Missing Properties??? Has anyone tried to open a cube into Business Intelligence Studio and the properties for the database vanished (project menu, properties) How do you get them back Tag: select inside update query?
  - 11
    - SQL Restore Rights When I tried to delete the created credentials, it gave me an error that they were in use, so I'm guess that is working, but I still can't see any of the backup locations. Does anyone have anything else that I can try Thanks! Brad Tag: select inside update query?
  - 12
    - Multiple masks in a For Each File Loop container I'm confused. If you only want to clean up certain files, then can you change your directory structure and have cleanup/scratch/trash stuff in one directory, while other files that you don't want to cleanup go in another Then you can wipe out everything in the folder. Or better yet, have different source folders for different file formats/extensions Not sure - maybe need some more context around what you're trying to do. But your original question - I haven't noticed a multiple mask option. Tag: select inside update query?
  - 13
    - stored procedures compilation Hi, Thanks for the prompt reply. I checked sys.syscacheobjects and it shows me how many time a procedure is used from the buffer but as the parameter value changes the procedure show plan removes from the buffer cache. I set ansi_nulls off but still it get removed from the buffer. I need help that if parameter value get changed the procedure show plan should not get removed removed from the buffer.. Regards Sufian Tag: select inside update query?
  - 14
    - Permissions for creating views In database permissions I have granted a user rights to create a view. When she tries to save the view (even save as) it automatically wants to save it to the dbo schema and says she does not have rights to save to the dbo schema. Two questions: 1) Can I set it up to where she can save a view to a schema which she is the owner 2) If not, then what permissions must be set to allow her to create / save views but not be able to create, etc. other objects such as tables and stored procedures Tag: select inside update query?
  - 15
    - Cant install SQL Server 2005 on vista.. SQL Server 2005 Enterprise Edition can be only installed on a *server* sku (edition) of Windows. All editions of Vista (yes, Ultimate Edition included) are Windows *clients*, therefore it doesn't allow you to install. To see the system requirements for various SQL Server 2005 editions, please refer to http://www.microsoft.com/sql/prodinfo/sysreqs/default.mspx . Note the workstation components of SQL Server 2005 are exceptions to the above rule, and can be installed on client Windows. That's why that checkbox is not grayed out in the image you attached. Tag: select inside update query?
  - 16
    - GENERATE(): Is there an alternative? CaveM, Thanks for the reply but this isn't what I'm looking for. As you mentioned, the MDX you provided will list the last patch applied per Machine. What I need answered is when a machine was last assessed and the status for each patch in order to answer questions such as "Are my machines 100% patched ". Tag: select inside update query?

abcdefgqwerty2

Is it possible for me to do something like

update table1 SET var1=something,var2=something2
from table1
(SELECT * from table2) as newtable
where newtable.field1=acondition

Top

Re: select inside update query?

Jens K. Suessmeyer

OK, the query (although not correct) also makes no sense to me. What are you trying to achive, try to explain the query you want to write in words.

HTH, Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Top