[RESOLVED]Getting only multiple records for all customers

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - New Log or append? Chris, Thank you very kindly! I take your comment about run-time, create/execute; that was spot on. Thanks for the code change - works like a charm ... ... but, I've looked and not found an answer, what is the "N" doing for me in SET @cmd = N'DECLARE ... Is it declaring that what follows is all an NVARCHAR string Tag: [RESOLVED]Getting only multiple records for all customers
  - 2
    - XML DML enhancement Can we hope for any improvements in next Katmai CTPs At first, that argument of the xml data type method "modify" may be not only a string literal (but also any string expression, local variable, etc) p.s. excuse my English Tag: [RESOLVED]Getting only multiple records for all customers
  - 3
    - ssis designer immediate window Where can I find instructions on how to use it Tag: [RESOLVED]Getting only multiple records for all customers
  - 4
    - Transfer table with schemas other than dbo My problem is very simple and that is that I'm trying to copy some tables between databases, but these tables are in different schemas. let's say I have dbo.tableA sch1.tableA sch2.tableA sch3.tableA And I just want to copy let's say sch1.tableA to a Different DB. If I use Transfer SQL Server Object task and select the table and save the package and try to open the task again, all the tables with name TableA will be selected!! it seems like although it does show the schema ( when I am selecting the table manually ) but it doe snot store the schema detail in the tablelist collection property of the task. Would please recommend any other way to achieve this. Many Thanks in advance Tag: [RESOLVED]Getting only multiple records for all customers
  - 5
    - Specifying a default date value for SPROC parameter sorry, got mixed up with column value default. for parameter default, you can only use constant value not functions CREATE PROCEDURE [dbo].[spGetCount] @start DATETIME = NULL, @end DATETIME = NULL AS select @start = isnull(@start, getdate()), @end = isnull(@end, getdate()) DECLARE @ReturnValue INT BEGIN Tag: [RESOLVED]Getting only multiple records for all customers
  - 6
    - Can't get at first row of input buffer...why?! oh, sorry Phil, I'm a bit confused. I have a bunch of rows coming into this script component....in fact I have 6 rows. I just want to access each column of each row and within each iteration I want to do a SQL INSERT. Now, I'm not entirely sure what you mean, but are you saying I don't need to even do I DO LOOP block What I do know is that I have tried to access each Row without calling NextRow but its throws that error: [PipelineBuffer has encountered an invalid row index value] Thanks...sorry if I'm not getting what you're saying...you might need to spell it out much appreciated! Tag: [RESOLVED]Getting only multiple records for all customers
  - 7
    - Exporting temporary table to excel spreadsheet That won't be a problem but what I'm really concerned here is the generation of the file. The procedure I'm running is collecting data and is supposed to output it. It's a report. Now the people who need this report want to do the minimal amount of work, which is why I think that if they need to start up excel and select objects and such, it won't be a good solution. What I want is a way of creating the excel file behind the scenes with a minimal amount of effort. Tag: [RESOLVED]Getting only multiple records for all customers
  - 8
    - How can I get a row count of CTE recordset? Hi everybody, I want to get a row count of temporary CTE recordset. My query is like this: With SQLPaging As ( Select Row_number() Over (Order by Row_num) as RowNumber, Row_num, Column_A, Column_B From OriginalTable ) Select * from SQLPaging Where RowNumber between 21 and 30 Let's assume SQLPaging definition contains 1,000 rows. If I use @@rowcount, it returns only 10. I.e. number of actual rows per page. What I want is that I want to know row count of SQLPaging CTE without extra counting query. I think SQLServer already knows how many rows CTE definition contains. The reason that I don't want to use extra counting query is that if SQLPaging definition has row performance query (like wild card(%) character search), extra row counting query takes simillar amount of loads. (Select count(*) from SQLPaging) Is there any way of getting actual CTE definition row count Thanks. Tag: [RESOLVED]Getting only multiple records for all customers
  - 9
    - add a reference in scriptComponent ADMariner wrote: It may also be valuable to point out that this same process can be used to call web services from script components/tasks as well (helpful for complete control over web service calls instead of using limited built in SSIS web service task). You can use wsdl.exe to create the proxy class then go through the same steps outlined above. And (in case anyone is still reading/interested ) in Katmai you'll be able use Web References just as you can in Visual Studio today. So no need to even use WSDL.exe -Jamie Tag: [RESOLVED]Getting only multiple records for all customers
  - 10
    - How to Drill Down a Parent Child Hierarchy in Reporting Services 2005 ? Try this!! It works fine! http://www.msdner.com/dev-archive/13/19-82-138549.shtm By Tag: [RESOLVED]Getting only multiple records for all customers
  - 11
    - how to properly write the mdx? Just a quicky, where are you putting this code Cheers Matt Tag: [RESOLVED]Getting only multiple records for all customers
  - 12
    - error when running SSIS package as a job I am running into an error when running a package using a scheduled job under SQL Server Agent account. I am getting following error: Date 6/12/2007 4:19:15 PM Log Job History (VistaODSFeed) Step ID 0 Server 006-DEVSQL2005 Job Name VistaODSFeed Step Name (Job outcome) Duration 00:00:00 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0 Message The job failed. Unable to determine if the owner (ACI\snasir) of job VistaODSFeed has server access (reason: Could not obtain information about Windows NT group/user 'ACI\snasir', error code 0x6ba. [SQLSTATE 42000] (Error 15404)). ACI\snasir is not the account under which SQL Server Agent service runs. However ACI\snasir has sa priveleges. not sure why am I getting ACI\snasir in the error and not the account under which sql server agent runs. ideas Tag: [RESOLVED]Getting only multiple records for all customers
  - 13
    - formatting in sql server Afraid to believe the ID value , It may have gaps rite Code Snippet Create Table #data ( [id] int , [score] Varchar ( 100 ) ); Insert Into #data Values ( '1' , '50' ); Insert Into #data Values ( '2' , '100' ); Insert Into #data Values ( '5' , '150' ); Insert Into #data Values ( '6' , '200' ); Insert Into #data Values ( '9' , '200' ); Insert Into #data Values ( '10' , '200' ); If you use SQL Server 2005, Code Snippet ; WITH CTE as ( Select *, Row_Number () Over ( Order By ID ) RID From #data ) Select up . id , up . score , dwn . id , dwn . score from CTE up left outer join CTE dwn on up . Rid = dwn . Rid - 1 Where up . RID % 2 = 1 If you use SQL Server 2000, Code Snippet Declare @Table Table ( [id] int , [score] Varchar ( 100 ) , [RID] int identity ( 1 , 1 ) ) Insert Into @Table Select Id , Score From #Data Order By 1 ; Select up . id , up . score , dwn . id , dwn . score from @Table up left outer join @Table dwn on up . Rid = dwn . Rid - 1 Where up . RID % 2 = 1 Tag: [RESOLVED]Getting only multiple records for all customers
  - 14
    - Out of memory while querying causes wrong data to be returned Running AS 2000, SP4. A couple of times while querying a cube, one member has got the wrong data displayad (the value are to low). No matter if I reload the query afterwards, restarting the query application etc, the same faulty value still gets displayed for that member. After I restart analysis services the value is correct again. No other changes, exact the same query etc. While analysing the event viewer I see that analysises services has run out of memory before this strange behaviour (MSSQLServerOLAPService - "The server is out of memory"). Could this out-of-memory error has caused the wrong value to be returned, and the somehow the value has got cached on the analysis server so it's not fixed until the server has restarted This is the only theory I have, and it's important to figure out the cause. Users are getting worried if they can't trust the data. The server is dedicated to analysis services and has 3GB memory and /3GB switch enabled. Memory conservation treshold is 2700mb and Minimum allocated memory is set to 1536mb. The query tool is an asp-application using ADOMD, but the same faulty value was returned while trying the query in MDX Sample App. Tag: [RESOLVED]Getting only multiple records for all customers
  - 15
    - MDX Calculations Script updates Pretty cool! I'll definitely give it a try. Thanks Chris for the idea.. Tag: [RESOLVED]Getting only multiple records for all customers
  - 16
    - OnInformation Event Firing Twice? In my SSIS package (which was created programatically) when I try to log details in OnInformation event I get all the entries twice. One with the source name as the taskhost.name property and another with a guid. I'm not able to figure out which component raises the event. Infact for all the events the sourcename comes as GUID. So I thought I'll override the events by inheriting IDTSEvents or DefaultEvents, But I'm not sure how to proceed further so that I can pass the sourcename properly instead of GUID. Code Block IDTSLogging .Log( string eventName, string computerName, string operatorName, string sourceName, string sourceGuid, string executionGuid, string messageText, DateTime startTime, DateTime endTime, int dataCode, ref byte [] dataBytes) Thanks Tag: [RESOLVED]Getting only multiple records for all customers
- Next
  - 1
    - SQL Server 2000 and ASP.NET connection problem Respectde Friends, My aspx page is trying to connect to a remote server with SQL Server 2000 installed on it, the ip of the server is 172.16.3.111 and the same is the instance of SQL Server, sql server is running in SQL Server Authentication mode, user id is "test" and password is "test123", name of database is myDB,the connection string which I am providing is as follows: "data source=172.16.3.111\172.16.3.111;user id=test;password=test123;initial catalog=myDB;" But I am getting the following exception: SQL Server does not exist or access denied. even if I try the following connection string: "data source=172.16.3.111;user id=test;password=test123;initial catalog=myDB;" than also I get the same exception even if provide the following connection strings: "SERVER=172.16.3.111;UID=test;PWD=test123;DATABASE =myDB;" "SERVER=172.16.3.111\172.16.3.111;UID=test;PWD=test123;DATABASE =myDB;" than also I get the same exceptions At last I tryed with the combination of ip address and sql server instance name in the datasource but still getting the same exceptions. Can u please tell whats the problem,is the connection string correct,the DBA has also registered me "test" as a user on the SQL server.One thing which I want to mention is that through enterprise manager I can connect and use sql server without any problems,also please note that in VS.NET when I try connecting using server explorer and test the connection than I connect successfully, than why is problem occuring in connecting throug aspx page. Please reply soon because I am very much confused and totally unable to resolve the problem. Tag: [RESOLVED]Getting only multiple records for all customers
  - 2
    - sql server security problem... Hi All! I have created a package, and now want to deploy it as a sql agent job. I have: -Set the security protectionlevel to "don't save sensitive" -saved a copy of the package to the sql server (using sql server authentication with 'sa' username and password) -Created a new job on the sql server - name is 'test' - owner is sql1 -added a step -type is Integration services package -Run as agent service account (can't change this) -package source is Sql Server -server is the same SQL server -log on to the server as 'sa' username and password -package points to the package I created earlier -No other configuration When I try and run it, it tells me that the 'sa' password is incorrect. When I look into the datasources tab of the step, the connection string to one of my connection manager has the sa username, but no password. when I go back to the package is BIDS, and look at the connection manager for that server, the password is blank. When I go back to the sql job, the step (which I set up to have SQL server authetication for the package source), is now suddenly back to windows authentication. Can anybody explain this I get various error messages when I fiddle about with the settings: "login failed for user 'sa'. " (I know the password is right, but maybe it falls over whne it blanks out ) "Failed to decrypt protected XML node "DTS assword" with error 0x8009000B "Key not valid for use in specified state.". (I don't have a key ) any help MASSIVELY appreciated... michal Tag: [RESOLVED]Getting only multiple records for all customers
  - 3
    - Combining Output PDF files LOL, Yeah I saw that, showed it to my boss. He said no to funding that trip..Oh well...LOL Doubt they would let me in anyways But hey if Microsoft wants to use me as a test case, I'm all for it!!! {WINK WINK, NUDGE NUDGE} Tag: [RESOLVED]Getting only multiple records for all customers
  - 4
    - Measure Weirdness have a measure that is a running total based on a formula: SUM({NULL:[Time].CurrentMember}, [Actual Work] / [Estimated Work] * [Planned Cost]) When this measure is rolled up for all time (for example 2007) the calculation is correct. For individual weeks or months, the calculation is not correct. See any issue here Tag: [RESOLVED]Getting only multiple records for all customers
  - 5
    - SQL2005 SP2 Dec CTP Sharepoint AddIn install problem Can you try Central Administration -> Site Actions -> Site Settings -> Site Collection features (not Site features) Thanks, Sharmila Tag: [RESOLVED]Getting only multiple records for all customers
  - 6
    - Reporting Services Instance not found i have the same problem and getting the same error. could anyone please help me. Tag: [RESOLVED]Getting only multiple records for all customers
  - 7
    - SSIS - Several Bugs in creating a simple task Thanks for your help. I know i'm being a bit harsh on SSIS. A logical assumption would be that it is difficult/impossible to limit the scope of the expression syntax if .NET is used. I don't know enough about .NET security policies to know if this holds true. It's just bothersome to fumble around yet another proprietary syntax, especially since I know .NET could have been used, and the .NET FCL has a lot more functionality than the expression syntax provides. I'd prefer not to assume, though :) Thanks for all of your help. Perhaps I just expected too much of SSIS. This is where I found the 'dynamic package' solution: http://sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx Tag: [RESOLVED]Getting only multiple records for all customers
  - 8
    - The SELECT permission was denied on the object 'tblName', database 'dbName', schema 'dbo' Hi, I'm in need of a few more pointers when it comes to SQL Server 2005 Security setup for accessing a database through IIS web services. There's loads of great advise in these forums which I've followed so pls forgive me if I've missed a post that finally resolves this. I know I'm so close but it feels so very far... Anyhow, here's the set up: - Server 2003 / SQL Server 2005 / .NET 2.0 / Visual Studio 2005 / Client XP PC The story so far: - Built an ASPX website in VS2005 on a client PC that connects to a SQL2005 db. No probs during development. - Copied website to WS2005 & configured IIS accordingly. No probs browsing non-db webpages. The problem: As soon as I browse to a page that extracts data from the db I get the following error message.. [SqlException (0x80131904): The SELECT permission was denied on the object 'tbl_location', database 'SmartDMA', schema 'dbo'.] ...plus a whole load of code that I've seen plastered over lots of forums. My configurations: - The website is in the default SQL Application Pool that has the identity set to 'Predifed : Network Service' (as I'd previously has a similar error; SQLException (blah blah): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection ) - IIS Website ASP.NET is v2.0.50727 - Website Authentication Methods / Enable Anonymous Access (ticked) Username: IUSR_SERVERNAME Password: *********. Intergration Windows Authentication (ticked). - SQL Server Mngt, Secuirty / Logins / NT AUTHORITY\NETWORK SERVICE lists the db in question with dbo as the Default Schema & public ticket as the db role membership. - connectionString = " Data Source=SERVERNAME;Initial Catalog=DBNAME;Integrated Security=true " providerName = " System.Data.SqlClient " /> What I've tried already based on forums advise: - Changing Server Properties to SQL Server & Windows Authentication mode. - Rather than using Integrated Security I've tried the SQL 'sa' account, but not 100% is I did that right (still got the same error anyway). The rest of the advise I've read appears to confirm the rest of my settings, but I'm still getting the error. Can anyone please shed any light on what I'm missing here. Feel free to ask questions on any configuration settings I've missed that may help. Many thanks in advance.. Tag: [RESOLVED]Getting only multiple records for all customers
  - 9
    - Substring and Patindex having an User Defined Function Hi All, Can we use the SUBSTRING or PATINDEX function with a User Defined Function as one of the parameters Something like this: Code Block Select SUBSTRING ( Select * from DB.dbo.Function( 'Parameter' ), PATINDEX ( '%\%' , Select * from DB.dbo.Function( 'Parameter' ), 8) Mannu. Tag: [RESOLVED]Getting only multiple records for all customers
  - 10
    - Why was ReadWriteVariable not populated/updated with a value of the dynamically generated string? qluo wrote: Then how should I approach this Script task doesn't work for my case What other way should I use Thanks! You're not understanding what I wrote. It looks like you've got it setup okay. When you run the package, it should pick up the correct value. Just because it's not there at design time doesn't mean it's not working. Tag: [RESOLVED]Getting only multiple records for all customers
  - 11
    - DTS Code works only with VS 2003 but not with VS 2005 ? Hi; I wanted to use the following code to run a DTS package from a 2005 Web Page code behind partial class. This code works fine in a VB 2003 module going against SQl Srvr 2000. Here is the code: (It initiates from a button click handler) Dim conn As New SqlConnection( "initial catalog=MY_Data;server= XYZ081552X7X441\TRSQL;integrated security=SSPI" ) Dim hold1 As Integer Dim hold_source As String = "" Dim hold_desc As String = "" Try conn.Open() Catch ex1 As Exception MsgBox( "The Test connection failed to open" & vbCrLf & ex1.Message) End Try MsgBox( "About to create a DTS object" ) Dim oPackage As New DTS.Package2Class (Compiler doesn't like this line) Type DTS.Package2Class is not defined. Dim oStep As DTS.Step (Or this one) Type DTS.step is not defined. oPackage.LoadFromSQLServer( "XYZ81552X7X441\TRSQL" , , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , "cpyPrinters2Excel" , ) For Each oStep In oPackage.Steps oStep.ExecuteInMainThread = True Next oPackage.Execute() For Each oStep In oPackage.Steps If oStep.ExecutionResult = DTS.DTSStepExecResult.DTSStepExecResult_Failure Then oStep.GetExecutionErrorInfo(hold1, , ) Else End If Next oPackage.UnInitialize() oPackage = Nothing conn.Close() Has Microsoft changed the DTS objects so that they work only with SQL Srv 2005 Thanks for your insights. Tag: [RESOLVED]Getting only multiple records for all customers
  - 12
    - i want know the difference between "SQL Server 2005 -Std Edition" and "SQL Server 2005 Std license" i need SQL server 2005 for my Application. but in my office they give 2 things. 1.SQL Server 2005 -Std Edition 2.SQL Server 2005 Std license but in my application they mention as SQL server 2005 thats all. so we need to buy SQL Serer 2005 license version. could u tel which one is best. Tag: [RESOLVED]Getting only multiple records for all customers
  - 13
    - Report Definition Is it possible to control the number of table rows that can be displayed by modifying the report definition Tag: [RESOLVED]Getting only multiple records for all customers
  - 14
    - Access To Dts.Variables Causes Exception Hey all...I'm pretty new to SSIS packages and things are coming along nicely. My problem is accessing variables in script tasks. I've created two variables (package scope, strings, readonly = false) - aDetailFiles and strDetailFile Within a Foreach Loop I loop through a folder. I've added a script task and within that I try the following: Dts.Variables("User::aDetailFiles").Value = Dts.Variables("User: trDetailFile").Value.ToString() + "|" + Dts.Variables("User::aDetailFiles").Value.ToString() Everytime this script executes I get the following errors: at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index) at ScriptTask_feac87c947ce4431a4fee0ba0e13631d.ScriptMain.Main() in dts://Scripts/ScriptTask_feac87c947ce4431a4fee0ba0e13631d/ScriptMain:line 25 I have searched this error message but the only thing I found was to set the ReadWrite variable property but I don't know where to set that. Expresion property set as false also. Any ideas Thanks. Tag: [RESOLVED]Getting only multiple records for all customers
  - 15
    - Property DefaultSchema is not available for Database [DatabaseName] Regarding this issue, I have a situation: We have a group of developers that we would want to configure as db_owners of database "B". All this developers belong to network group "A". We associated this group to db_owner role in database "B" and thought it would be enough to configure the desired permission in the db. The problem is when some developer (belong to group "A") tries to create a table in the db "B", get's the error: "Property DefaultSchema is not available for Database 'B'. This property may not exist for this object, or may not be retrivable due to insufficient rights. (SQLEditors)". We tried then to configure the DefaultSchema for the group, but are unable to, because of the behaviour you refered earlier. So, in this situation, how can I configure a specific network group to be db_owner (or have some other role) Is it possible in SQL Server 2005 Don't tell me we have to configure all users, one by one... In SQL Server 7/2000 we have no problem with this. Tks. Tag: [RESOLVED]Getting only multiple records for all customers
  - 16
    - Cannot insert duplicate key Ok. One simple & easy way is adding the identity column temporarily, fetch the data using the following query and drop the temp identity column from the table, Code Snippet Create Table #duplicates ( [ID1] int , [ID2] int , [ID3] int , [Name1] Varchar ( 100 ) , [Name2] Varchar ( 100 ) ); Insert Into #duplicates Values ( '1' , '1' , '1' , 'SQLServer' , '2000' ); Insert Into #duplicates Values ( '1' , '1' , '1' , 'SQLServer' , '2005' ); Insert Into #duplicates Values ( '1' , '1' , '2' , 'SQLServer' , '2005' ); Insert Into #duplicates Values ( '1' , '1' , '2' , '.NET' , '1.1' ); Insert Into #duplicates Values ( '1' , '1' , '3' , '.NET' , '1.1' ); --Temp Identity Column Alter table #duplicates add tempid int identity ( 1 , 1 ); --Required Unique data select data . id1 , data . id2 , data . id3 , data . Name1 , data . Name2 from #duplicates data Join ( Select id1 , id2 , id3 , min ( tempid ) tempid from #duplicates Group by id1 , id2 , id3 ) required On required . tempid = data . tempid --Droping temp identity column Alter table #duplicates drop column tempid Tag: [RESOLVED]Getting only multiple records for all customers

den2005

Hi,

How can I get desired data results from Sample Data given using SQL statement Preferably without using Cursors and simple as possible.

Sample Data:

OrderID CustomerID ProductID
2 2 2
3 3 null
4 3 2
5 2 2
6 4 3
7 5 4
8 2 10
11 3 5
13 5 8
14 6 9

Desired Results:

OrderID CustomerID ProductID
2 2 2
4 3 2
5 2 2
7 5 4
8 2 10
11 3 5
13 5 8

Top

Re: [RESOLVED]Getting only multiple records for all customers

Arnie Rowland

It is not easy to 'guess' what criteria you have used.

Obviously, eliminating OrderID 3 is because nothing was ordered.

But why have OrderIDs 6 and 14 been left out

Top

Re: [RESOLVED]Getting only multiple records for all customers

ManiD

The following query will work for You..(multiple records for all customers - customers who have multiple records)

Select * from Orders Where
CustomerId in (Select CustomerId from Orders Group By CustomerId Having Count(ProductID) > 1)
and ProductId is NOT NULL

Top

Re: [RESOLVED]Getting only multiple records for all customers

den2005

Thanks for reply, Arnie.

Thanks for the answer, ManiD.

Top