Data from Derived Table

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - Sqlserver In Oracle to handle the general exceptions we will write the program as procedure raise_exc(i number,msg out varchar) as i_exc exception; begin if i>10 then raise i_exc; end if; exception when i_exc then msg:='Value of i <10'); end; / In case of Sqlserver program how to raise these type of exceptions and how to handle it Tag: Data from Derived Table
  - 2
    - Transparent Encryption I still don't have a pure table solution working, but here's a solution using views, seems to work great, thanks Chris! http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html Tag: Data from Derived Table
  - 3
    - Matrix grouping I have a report with the Month attribute as the column group and specific measures as the row groupings. Now, here's my delima. The months are not being displayed in order. They look like this: Jan Feb May Jun Jul Aug Sep Oct Nov Dec Mar Total Why is it doing this Here's a view of my matrix in layout view... Month_Name Fatal Crashes =sum(fatal_crashes.value) Injury Crashes =sum(injury_crashes.value) Property Damage =sum(Prop_Damage.value) Total Crashes =sum(Total_crashes.value) Chicago Crashes =sum(Chicago_crashes.value) Crashes Located =sum(located_crashes.value) % Located =sum(percent_located.value) any help would be greatly appreciated!! THANKS! Tag: Data from Derived Table
  - 4
    - How to backup CE database Tables and restore to newer database? "Doing a backup" is a simple matter of closing all database connections from within your app, and mak a file copy of the sdf file "Restore" is a matter of making an older sdf file the "active" database file. You could also consider doing backup a every app start/exit, if required (disk space allowing). Update package: Create a VS MSI setup project for you app that does not include the sdf file. Tag: Data from Derived Table
  - 5
    - Adding CRLF to nvarchar(max) column text Ah, text formatting is really a function of how you setup the bound controls and the raw data. You might have to do a Replace operation to search the data for all paragraph markers and replace them with extra line feeds. Frankly, this is problemeatic as once the data is so modified (and saved), the next time it's loaded if your code repeats the operation you're pooched. You might also be able to update the server-side data on a one-time basis using a stored procedure but I expect you'll need to modify the program that imports the data in the first place to handle the CRLF sequences to better match your reqirements. hth Tag: Data from Derived Table
  - 6
    - EncryptSensitiveWithUserKey - question That's what I would do. I usually set passwords through configurations (or use integrated security, and eliminate the need for passwords altogther). Tag: Data from Derived Table
  - 7
    - @@FETCH_STATUS Reset There are a few 'tools' that make the DBA's job much simplier. One of those tools is have a Calendar table in the database. With a Calendar table, it is simple to JOIN against other tables with date values and derive the span of time between such dates. You may explore using a Calendar table with this resource . With a Calendar table, you can easily meet your requiremens with a single query. You need a FETCH before the WHILE statement. And then normally, the FETCH inside the loop is the last line -not the first line. Tag: Data from Derived Table
  - 8
    - Object reference not set to instance of the object while trying to sync. could you plese still be able to tell under what circumstanaces you saw this error Maybe te user running the wizard did not have enough permissions or something Tag: Data from Derived Table
  - 9
    - OLE-Db connection in Transformation Script Component Hello, Using the following documentation as a guide: http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx I instantiated a new script component into an existing Data Flow in my SSIS project. In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project. My Connection Manager is of the type oOLEDB. I then opened up the script designer and added the following lines of code where it said "Add your code here" Dim myConnManager As IDTSConnectionManager90 = _ Me .Connections.ECFconnection Dim dbConn As OleDb.OleDbConnection = _ CType (myConnManager.AcquireConnection( Nothing ), OleDb.OleDbConnection) When I test run the project I get the following error and the new script component is red: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. I know the database connection works since I am using it in a component that executes before this new script component. I am stuck...Any suggestions Tag: Data from Derived Table
  - 10
    - Installing Client Tools Refer to this http://support.microsoft.com/kb/257716 page that clears about licensing policy on client tools, though it is for SQL 2000 all the information is still applicable to SQL 2005 in this case. I would also ask you to refer to the local Microsoft support for more information on licensing, as x64 is bit new in recent times. Tag: Data from Derived Table
  - 11
    - Comparing Large Tables chip_levis wrote: Well, when you're doing a lookup for tens of millions of records against another table with tens of millions records I would think it becomes pretty considerable since you're doing a row-by-row comparison. That's true. The initial cartesian product is quite large! chip_levis wrote: Do you think it would be faster to stage the tables and try comparing them through T-SQL or just use SSIS's lookup component If you can keep the result set small in the lookup component, I would expect that would perform better because of the way it caches its records. That is to say you must include ONLY the columns necessary to perform your work and nothing else. Select just those columns in the lookup SQL upfront. If you're going to select all of the columns, then you could run into memory/paging problems, which may end up in slower performance compared to T-SQL. Tag: Data from Derived Table
  - 12
    - How to delete rows in tables... Barry Andrew wrote: BE ULTRA CAREFUL!!! - I would suggest a backup or at least a test on a staging system if available. DELETE FROM fsalesinvoiceline, dsalesinvoiceheader WHERE fsalesinvoiceline.salesid = dsalesinvoiceheader.salesid AND dsalesinvoiceheader.billtocustomer = 'INDTAST DEBITORNUMMER' Like I said the data in these tables are copies from our OLTP database (MBS Navision) they are emptied every night and filled with new data, so nothing will get lost if something goes wrong. I will try your suggestion tomorrow. Tag: Data from Derived Table
  - 13
    - Maximum height for table So you're saying that you want to cut off the rest of your data if it goes past the height you specify Tag: Data from Derived Table
  - 14
    - any *simple* external activation examples available? BOL only seems to say that you can do it w/o really showing how, and the ExternalActivator sample at gotdotnet.com contains so much functionality I'm not sure what's required just for the external activation. Are there any docs or samples out there that focus on how to do it w/o obscuring the matter with a bunch of other functionality (I prefer docs to project samples, b/c the samples tend to have hacks like hardcoded paths and connection strings so that they rarely work correctly right out of the box.) TIA Tag: Data from Derived Table
  - 15
    - My SSIS problems If only they'd add the mind reading interface I keep requesting Tag: Data from Derived Table
  - 16
    - Filtering Problems I am stumped on what is probably a simple glitch. I have an SSRS 2005 server report based on a stored procedure dataset, which accepts a single parameter. The parameter value originates from a session variable when the user opens the aspx page containing the reportviewer control (Visual Web Developer 2005). I've configured this parameter as a hidden parameter in the RDL design. In this configuration it functions as intended in both the development environment and when deployed. Next, I tried to add user prompts to implement report server filtering (in addition to the database filtering in the source SPROC). I added the parameters, set up the data sources for the prompt values, set up the filter expressions, did everything that I can find to implement this process. When I preview the report in the development environment, it works as intended. When I deploy it and attempt to run it from my http://localhost environment, though, it fails. There's no error message or anything...I don't even get the "Report Running" icon with no rows returned...what I get is prompts which function correctly (showing the right values and letting me select one), but when I click "Run Report" I just get an immediate screen refresh with the prompts reset to the default "<Select a value>" setting. And no report. I've obviously missed some setting somewhere, for it to work in dev but not when deployed. Any suggestions will be GREATLY appreciated. Tag: Data from Derived Table
- Next
  - 1
    - Cannot load Data Flow tasks When I copy over an SSIS package I have been developing from my laptop to my desktop with Windows File Sharing (shared folders) across a home network, the moved package fails to load properly. I can see the Control Flow tasks but not the Data Flow tasks - they simply disappear! I have update the Connection Managers to point to the new machine, and tested them (OK). Its the same as this issue posted here And Here Creating an empty package and clicking to create a new data flow cause this error: TITLE: Microsoft Visual Studio ------------------------------ Failed to create the task. ------------------------------ ADDITIONAL INFORMATION: The designer could not be initialized. Microsoft.DataTransformationServices.Design) Seems the only way is to uninstall and reinstall Client Components. How do I uninstall and reinstall Client Components when I try to do that, SQL Server Setup tells me "None of the selected features can be installed or upgraded. Setup cannot procees since there is no effective change being made to the machine...." I had selected "Workstation components, Books online and Development tools". This is already installed - so how I can reinstall it if I get this message above which does not let me proceed. Tag: Data from Derived Table
  - 2
    - SQL 2005, OPENQUERY and security context switching Hi, I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine: EXECUTE AS LOGIN = 'link_user' SELECT * FROM OPENQUERY ( linked_server , 'SELECT * FROM ... ) But when I use the exact same statements in a stored procedure I get the folllowing error: Msg 7416, Level 16, State 1, Procedure linktest2, Line 5 Access to the remote server is denied because no login-mapping exists. I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures! Is there another workaround then to create all users in the linked server security Rgds Bertrand Tag: Data from Derived Table
  - 3
    - Linking Oracle view to SQL Server express You are using the MS Oracle driver, as Jens suggestions you might consider using the driver provided by Oracle. You can find an example of using Openquery in BOL, the topic is here . When ever you're looking for an example of how to use a specific function, it's a good idea to search BOL first since all T-SQL commands are documented there. Regards, Mike Tag: Data from Derived Table
  - 4
    - Query problem involving DateTime column thanks for correcting my mistake. btw, any idea why the resultset still return rows when there is no such date in the DB Tag: Data from Derived Table
  - 5
    - Pivot fields into a single column WVUProgramer wrote: Thanks, but I can't use create table or insert. Appreciate the effort though. You don't need to - he used the create table and insert to generate sample data so that his entire code example would run in its entirety. You just need to use the select part of it (possibly modified to meet your exact requirements) Tag: Data from Derived Table
  - 6
    - Access project connecting to SQL 2005 How does Access connect to SQL Server Are you granting SELECT on the schema to the same user that you are adding to the db_datareader role Thanks Laurentiu Tag: Data from Derived Table
  - 7
    - Filters - Report Builder I have created a report showing both first name and last names with the following filters. Both of them are prompted parametres Last Name Contains ------------------ First Name Contains ------------------ When users enters both then we get the results back. I want the first name to be optional such that if user enters something for the first name then take it into the search criteria else show all the first names which matches the last name criteria. Example: Last Name = "SMITH", First Name = (user has not entered and NULL check box is checked). I don't get any results since this filter translates to find all the records which has the last name SMITH and First Name IS NULL), Instead I want this to be select all the records which has the last name SMITH and First Name <> EMPTY. Is there a way I can do this using the fiter magic Thanks. Tag: Data from Derived Table
  - 8
    - Connection Managers Area not coming up And do you have a package opened Do you have the control flow such that you can add tasks to it Tag: Data from Derived Table
  - 9
    - How to make this query... No, let's examine the select I proposed : delete from db_stockprices where symbolid in (select Symbolid from db_stockprices where Date<>@yourdatavalue) let's say "select Symbolid from db_stockprices where Date<>' 1/3/2007' " return "2" and "4" so then select for deleting will be transformed in delete from db_stockprices where symbolid in ("2","4") so will be deleted all rows you desired. So, this solution I think will work. Tag: Data from Derived Table
  - 10
    - What is the best book to start from scratch? Dear Doug, Everything is available in Books online you just need to search in........just type "Create database" you'll get it.... or else just click the contents tab in books online and go through the topics you wish to learn..........Books online is a good resource.........if you wish to learn more you can try out the Microsoft Virtual labs..refer the below link, http://msdn2.microsoft.com/en-us/virtuallabs/aa740409.aspx Tag: Data from Derived Table
  - 11
    - Mirroring on separete domains We have 2 servers each having a domain (i.e. 2 domains) running Windows Server 2003 Standard Edition Service Pack 1 We would like to have one of the servers replicate/mirror the data on to the other but we are getting this error The error produced from the mirroring part is: TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'CTSM'. (Microsoft.SqlServer.Smo) ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://UM_db_live.UMlive.local:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error : 1418) Thank you. Tag: Data from Derived Table
  - 12
    - Trigger with access to remote server Do you need to execute the command on the linked server synchronously HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- Tag: Data from Derived Table
  - 13
    - problems at beginning of set up Am I using the correct NotificationServicesHost I'm using the full name of the computer I'm using. Tag: Data from Derived Table
  - 14
    - Trigger? Hassano wrote: delete the orderdate ( if the orderdate is 1/1/1900) And as Madhu suggested, any datetime value passed in as an "empty string" [''] will be 'assumed' to be to be zero, which translates to '01/01/1900 00:00:00.000'. That happens because a datetime value is inherrently a number, and since numbers can't have string values, even empty strings, SQL Server converts the empty string to zero. Your alternative would be to use a TRIGGER to 'translate' the value of '01/01/1900 00:00:00.000' to NULL. Most of the time, it is better to have NULL to indicate missing (or unknown) values -though it does then take a little extra effort to account for NULL when doing various data routines. A 'better solution would be to have your application check the textbox value, and it if is a empty string, then assign the parameter dbNULL instead of the textbox value. Tag: Data from Derived Table
  - 15
    - Report connection fails with DNS name Hi, I am facing problem while setting connection string in Report Service project. Problem is when I specify DNS name instead of machine name as the Server in the connection string. When I specify DNS name in the connection string it gives error that "A connection cannot be made.Ensure that the server is running" on click of "Test Connection" Also I am not able to view reports directly through IE. I.e. http://<DNS_NAME>/Reports . When I access Reports, it prompts me to enter username/password. Even after specifying valid username, it shows me the prompt again n again. Is there some known issue with Reporting Service or with IIS setting not able to connect to a DNS name I did not face with any issue when I specified DNS name in the connection string of Analysis Service project. Did anyone encountered DNS related problem with SQL Server 2005 Reporting Service We also verified that: 1: Ping with both machine name and DNS name returns same result. 2: nslookup with both machine name and DNS name resolves to same IP. 3: Tried putting the DNS name in the hosts file. Eagerly awaiting some positive reply. Note: I am working with SQL Server 2005 Standard Edition. Tag: Data from Derived Table
  - 16
    - Creating Stored Procedure in SQLServer2005 Can you provide me with more details As how to reach the Stored Procedure and how to Execute it Thank you Tag: Data from Derived Table

Duncan McC

I am looking to run two different queries.

the first one returns about 6000 numbers. i.e 123454, 15432, 2343545 etc

My second query i want to use the results from the first query as a where clause

i.e

where Number in ('123454','15432','2343545')

Clearly i dont want to type out 6000 numbers in a query. How do i use the data generated in the first query as the where criterea in the second query.

Top

Re: Data from Derived Table

Manivannan.D.Sekaran

You can use the following query...(subquery)

Select Numbers From SomeTable -- Which returns 6000 numbers

on your second query..

Where Number in(Select Numbers From Sometable)

Top