Any Alternative Way To Retreive Data

May 19, 2004

Hi: Guys
Given the table below I want a select query that returns the AccountRepID with the largest single sale for each RegionID. In the event of a tie choose any single top AccountRepID to return.

CREATE TABLE [Sales] (
[SalesID] [int] IDENTITY (1, 1) NOT NULL ,
RegionID] [int],
[AccountRepID] [int],
[SalesAmount] [money]
)

If the data were
salesid,regionid,accountrepid,salesamount
1,101,31,$50
2,101,32,$25
3,102,31,$25
4,102,32,$25
5,102,31,$15

The query should return
regionid,accountrepid
101,31
102,31 or 102,32


Is there another way to get the data other than the following query:

select regionID,accountrepid FROM Sales
where salesamount in
(select max(salesamount) FROM Sales group by regionid)

Thanks

View 2 Replies


ADVERTISEMENT

How To Retreive Data From Excel To SQL SERVER Database Table

Oct 6, 2007

Hi Friends,
I am Using SQL SERVER 2005
I need to retreive data from Excel file to SQLSERVER table..
Thanks in advance
Regards
Rajkumar.M 
 
 

View 1 Replies View Related

How To Retreive Data From Excel Into SQLSERVER Database Table

Oct 6, 2007

Hi friends,
I need to retreive data from Excel into SQLSERVER database table
give me reply asap.
Thanks in advance...

View 4 Replies View Related

How To Retreive Data From Nvarchar(max) Column, Using Com.microsoft.sqlserver.jdbc.SQLServerDriver?

Dec 31, 2007

I've looked through msdn and found an example, but it doesn't work. The problem is that when i use such construction

ResultSet rs1 = stmt.executeQuery("SELECT TOP 1 * FROM sys.check_constraints");
rs1.next();
Reader reader = rs1.getCharacterStream(17);

reader is set to null,but it mustn't be null cause i see some data through management studio in 17th column of sys.check_constraints table.

View 3 Replies View Related

How To Retreive Data Depends On Another Data ...

Mar 13, 2007

my application will add and delete and update records in db
my problem is when to update and delete ..
I have two dropdownboxes one to retreive servers and one to retreive dbs in the server ...
SqlDataAdapter daa = new SqlDataAdapter("SELECT * FROM DB_tbl"+","+"Servers_tbl where Servers_tbl.Server_Name == '"+ DropDownList4.SelectedValue +"' ", sqlConnection1 );
 
this is the structure of each table >>
servers_tbl : SRV_ID,Server_name
DB_teb      : DB_ID,SRV_ID,DB_name
 the dropdown box retreive just the names ... so what is the base of where part of the command
hint : the DB_tbl does not have the server name but has the sever id   

View 1 Replies View Related

Alternative For LEFT() And CHARINDEX() That Works With Text Data Type

Jun 26, 2007

I have a stored procedure that receives a list of ids, to get the emails of each of those ids. The problem that I'm having is that I'm using a char data type that is max length is 8000, but the contact lists are getting bigger and soon they might reach the variable max length. The contact list that I receive will look something like this "1234,67523,67875,789687,", I'm using "," as a separator. Right now what I do is this
@array_value = LEFT(@ContactList, @separator_position - 1)
 
The LEFT function doesn't work with data types text and ntext. But I'm in need of a string data type with a max length bigger than 8000. So I will apreciate if anyone knows of another function that does the same or similar and works with text data type, or any other ideas will be welcome.
 
Thanks,
 Erick

View 8 Replies View Related

Export Table Data Into A Series Of Insert Statements (or Alternative)

Jan 29, 2008

Hi all,

I have a table which I would like to export to a series of insert statements (in a file maybe). Is this possible somehow?

Alternatively, I could use an existing xml document which contains table metadata (table name, column names,types etc) to transfer data from these particular columns to another database replica.

Thanks,
Yannis

View 10 Replies View Related

How Do I Retreive Id Of Just Inserted Row?

Nov 3, 2007

I'm creating a web application that has user input on 3 seperate pages. Each page prompts the user for specific information and at the bottom of the page the user will click on the "Submit" button to post the info from the form to the database table.
Once the user has submitted the first page of information how do I retrieve the ID from the CustID column of that row so I can use the Update function to add additional info to that row when the user clicks on the submit button on page 2 & 3. I don't want to hold all the information in variables until the end in case they bail out of the form.
TIA
Steve

View 7 Replies View Related

Retreive Records From Sqlserver

Oct 16, 2006

hey guys, i getting a minor problem here. Hope u guys may help me =) I would like to count all the records from a table ' member ' and store the value inside a variable ' iCnt ' i wrote like tis:cmd.commandtext = " select count(*) as total from member" dim iCnt = cmd.ExecuteScalar Hmm.. I am wondering whether my code is correct, it seem like cant work =(Any help is appreciated, thanks u guys. 

View 1 Replies View Related

Update Field Without Retreive

Jan 24, 2007

if a db field "mynum" of type int contains for example the number 543.Is there a way I can update this field without first selecting the value?so for example set the the current value to +1
 

View 1 Replies View Related

Retreive Only Part Of The Query?

Sep 16, 2004

Is there a chance to retreive let's say rows positioned from 30-70 if a query produced result that contains 100 rows?

View 3 Replies View Related

Retreive A Table-Urgent

Mar 30, 2001

Hi,Is it possible to retrieve a single table from a backup in SQL 7.0?
TIA
Jay

View 1 Replies View Related

How Do I Retreive 0 For Null Value In A Query

Nov 22, 2005

How do i retrieve 0 for null value in the following query

select customerid, count(*) from calls_new_today c, customerdnis cd where c.cpn=cd.dnis and cd.enddate is null
group by customerid
order by customerid


what i mean is when count(*) = 0 no value is returned i want a Zero in the output how do i get it?

View 7 Replies View Related

Retreive The Middle Of A String

Apr 5, 2007

Im having a dumb moment... what is the easiest way to get the third and fourth character of a string???

Thanks :)

View 7 Replies View Related

Want To Retreive Update Row In Table

May 10, 2007

i want to insert a row from t1 to t2 if any of the row is updated in the t1. say if Nth row is updated then the values of the row should be inserted into t2. inside the trigger i need to retrieve the row which is updated, how to do it? any specific global variable like @@identity is there for updation?

View 6 Replies View Related

Pb To Retreive Max Value With Aggregate Subquery

Jul 20, 2005

I would like to know which products are my best sells by sellers, but iwould like to retreive this info by product id, seller id and the totalamount of sells for this product.My Sells table is :Seller_idProduct_idTotaldate_s1 2 1020/05/042 4 1512/05/043 5 2206/06/041 5 1807/06/044 8 1213/05/047 2 1119/05/043 4 1421/05/042 4 1418/05/041 5 1817/06/042 5 5008/05/04etc....I know how to retreive the total sells by product id and seller idSELECT Seller_id, Product_id, SUM(Total) AS totalFROM SellsWHERE date_s > '01/05/04'GROUP BY Seller_id,Product_id order by Seller_idSeller_idProduct_idTotal1 5 361 2 102 5 502 4 293 5 223 4 14I would like retreive only the max of total, and the Seller id andproduct id, like this :Seller_idProduct_idTotal1 5 362 5 503 5 22How can i do without using a temp table ?Thanks for your help.

View 3 Replies View Related

Retreive Password From Application

Apr 15, 2008



Hello Everyody,

Some background: I have a SQL server 2000 server that I just restored from backup after complete OS reinstallation. I have retored all databases (except the master) and I still have the mdf and ldf file from before reinstallation (master.mdf as well).

Now my problem is that I have 1 application that use a specific SQL user with a password that the application choose itself and of course this application doesn't work anymore because I didn't restore the sql users (I use windows authentification as much as possible). I cannot do anything to the application to use another password but I know the login used.

My questions is "How to I recover the password?". Is there a way to log the password that this application send to sql or from the old master.mdf file?

Any help would be greatly appreciated.

Bruno.

View 7 Replies View Related

How To Retreive A Row From A Datatable Based On Row Id

Mar 14, 2008



hi,
can anyone tell me how to retreive a row from a table using rowid (For example i want to retreive fifth row from a table)

View 3 Replies View Related

How To Upload Images To SQL2005 (using C#) And Retreive Them

Sep 13, 2007

Hi guys,    I'm creating a webSite and i'd like to save images to my db. I've been using fileUpload to get the image i want to store, but i can't get it right! After "mastering" the storage of my images , i'd like to be able to retrieve them. I allready have SPs created for both storage & retrieval of images!Can anyone help me please? Thanks,superJB

View 3 Replies View Related

Query To Retreive User Tables In Sql Db

Jan 2, 2004

Is there a way to create a query that will return all user tables inside a sql db

Thanx

View 4 Replies View Related

Send Mail Can Retreive Log File

Apr 1, 2008

I have developed a SSIS package on my local box and everything worked fine until I moved it to the production server. I have a Send Mail Task that is triggered by any of the other processes when they fail. It sends an email to the Admin with the current log file. My issue is that when it attempts to grab the log file (as an attachment) it fails with the following error message:

OnError,BILBO,UMIALIKJamesT,Email George,{a73bfc5c-d68e-426a-98ad-55e701670470},{677B8238-8697-454F-8F8A-DD6378E93455},4/1/2008 10:25:28 AM,4/1/2008 10:25:28 AM,-1073573429,0x,Either the file "D:SQL200590DTSPackagesPPROutputoutput.txt" does not exist or you do not have permissions to access the file.


It certainly exists and I've open all permissions up on the folder. What I find even more interesting is that the Package is just a single directory up from the Output file. The Package resides at D:SQL200590DTSPackagesPPR and is run via a batch file. It seems to me that it should have no issue grabbing this file and sending it, and when I don't attach the file it sends just fine.

Thank you in advance. If you need anymore information just let me know and I'll dig it up.

Peace,
James

View 6 Replies View Related

Way To Retreive Database Role Permissions

Mar 13, 2008



I want to allow administrators of the program to change the permissions of the database roles. Is there a way to retreive the the specific permissions granted an denyed to a user and a database role. For instance, a store procedure that you call, pass it the database role and you get whether SELECT is allowed on table1, or if UPDATE is denied on table2, etc.

View 3 Replies View Related

Retreive @@Identity From Stored Proc Call

May 3, 2007

Hi all, I've been struggling with this one for a while, but am still doing something wrong: I have three tables which need to be updated. I have a stored proc which accomplishes the first writes the data in for the first 2 tables. The last table is a one to many, so needs a seperate stored proc which will be called multiple times depending on the number of items in the order.  My only question I am trying to get to here is: How do I get the first stored proc to return me the primary key value from the 1st insert (NOT the second)? I've tried a few different methods: the current one shown below returns me  "2", as in the number of inserts performed.   Dim InsertCmd As New SqlCommand("WriteOrder", oSQLConn)
InsertCmd.CommandType = CommandType.StoredProcedure
InsertCmd.Parameters.AddWithValue("@CartTotal", Session("CartTotal"))
InsertCmd.Parameters.AddWithValue("@CARDFNAME", BillingInfo("CARDFNAME"))
InsertCmd.Parameters.AddWithValue("@CARDLNAME", BillingInfo("CARDLNAME"))
...
InsertCmd.Parameters.AddWithValue("@CONTACTEMAIL", BillingInfo("CONTACTEMAIL"))
InsertCmd.Parameters.AddWithValue("@COMPANYMATCH", 0)
InsertCmd.Parameters.AddWithValue("@RECNUM", 0)
Response.Write("---" & InsertCmd.ExecuteNonQuery().ToString() & "---")


---------------------STORED PROC---------------------

ALTER PROCEDURE [dbo].[WriteOrder]
@CartTotal float,
@CARDFNAME varchar(30),
...
@CONTACTEMAIL varchar(100),
@COMPANYMATCH bit,
@RecNum int = 0 OUTPUT
AS

INSERT INTO [dbo].[ORDER]
([OrderDate], [OrderTotal], [CARDFIRSTNAME], [CARDLASTNAME], [BILLINGADDR1], [BILLINGADDR2], [BILLINGADDR3], [BILLINGCITY], [BILLINGSTATE], [BILLINGPOSTALCODE], [BILLINGCOUNTRY], [BILLINGPHONE], [BILLINGEMAIL], [CCTYPE], [ACCOUNTNUMBER], [CARDEXPIREMONTH], [CARDEXPIREYEAR], [CVV2], [TransactionID], [TransDateStamp], [ProcessorAuthCode])

VALUES
(GetDate() ,@CartTotal , @CARDFNAME, @CARDLNAME, @BILLINGADDRESS1, @BILLINGADDRESS2, @BILLINGADDRESS3, @BILLINGCITY, @BILLINGSTATE, @BILLINGPOSTALCODE, @BILLINGCOUNTRY, @BILLINGPHONE, @BILLINGEMAIL, @CCTYPE, @ACCOUNTNUMBER, @EXPIRATIONMONTH, @EXPIRATIONYEAR, @CVV2,@TransactionID, @TransDateStamp,@ProcessorAuthCode)

SET @RecNum = @@IDENTITY

INSERT INTO [dbo].[CONTACT]
([ORDERID], [CONTACTFNAME], [CONTACTLNAME], [CONTACTADDRESS1], [CONTACTADDRESS2], [CONTACTADDRESS3],
[CONTACTCITY], [CONTACTSTATE], [CONTACTPOSTALCODE], [CONTACTCOUNTRY], [CONTACTPHONE], [CONTACTEMAIL], [COMPANYMATCH])
VALUES
(@@IDENTITY , @CONTACTFNAME, @CONTACTLNAME, @CONTACTADDRESS1, @CONTACTADDRESS2, @CONTACTADDRESS3, @CONTACTCITY, @CONTACTSTATE, @CONTACTPOSTALCODE, @CONTACTCOUNTRY, @CONTACTPHONE, @CONTACTEMAIL, @COMPANYMATCH)
RETURN @RecNum
GO

 I've also tried returning parameters like this, with no luck:        InsertCmd.Parameters(32).SqlDbType = SqlDbType.Int        InsertCmd.Parameters(32).Direction = ParameterDirection.ReturnValue        Response.Write("---" & InsertCmd.Parameters(32).Value() & "---")Any help is greatly appreciated! 

View 4 Replies View Related

SQLDataSource - How To Retreive The New GUID Value Of A New Inserted Record?

Jan 28, 2008

I am using ASP.NET 3.5 with SQLDataSource, when a record is inserted is there a way to retreive the newly created GUID value in a field?
I am currently using SELECT @NewID = SCOPE_IDENTITY() to retreive the newly created record unique ID field, but I need the secondary field GUID field's value instead in this case.
Thank you.

View 7 Replies View Related

SQLDataSource - How To Retreive The SQL Select Statement With The Values Of The Parameters

May 1, 2007

Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
 
Thank you

View 4 Replies View Related

Retreive Html Code From Xmlfile, Insert Into Table

Nov 15, 2007

I use the following query to shred an xml and insert it into a table, but I want to have the whole html structure into the column to be able to present the formatted text in Cognos 8 BI.

WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
INSERT INTO TMP_ATGFORSLAG (AtgForslagDesc)
SELECT
AtgForslagRad.value('(/my:AtgForslagRad/my:AtgForslagDesc)[1]', 'varchar(2000)') AS AtgForslagDesc
FROM dbo.DeklAtgForslagXml

The structure of the source in the xmlfile I want to import is:
<my:AtgForslagDesc>
<html xmlns="http://www.w3.org/1999/xhtml">
<ol>
<li>Text1...</li>
<li>Text2...</li>
<li>Text3...</li>
</ol>
</html>
</my:AtgForslagDesc>

How do I shred not only the text but the whole starting <html> to finishing </html> and insert it into a table I would be very happy.

View 1 Replies View Related

Alternative For DTC

Jul 30, 2007

Hi,
i am using the DTC in my code to connect to two different servers on the network through a SQL query which is unfortunately very slow; can u please guide me with an alternative for the same

Thanks

View 17 Replies View Related

Alternative Way

Jul 20, 2005

SELECT *FROM organizationWHERE (departmentID = divisionID) AND (divisionID = branchID) AND(branchID = sectionID) AND (sectionID = unitID)Is there anyway I can make this query more simlified w/o repeating thesame column in the where clause?thankss/RC

View 3 Replies View Related

@@IDENTITY Alternative

Dec 30, 2007

Hi everyone,
I'm trying to come up with a replacement for @@IDENTITY, because I have SQL code I want to make more portable.

Original:ID = MyDataLayer.Execute("INSERT INTO X(a,b,c) VALUES(A,B,C); SELECT @@IDENTITY")
Proposed solution:
lock(MyDataLayer)
ID = MyDataLayer.Execute("SELECT MAX(id)+1 FROM X")
if(ID==null) ID=1
MyDataLayer.Execute("INSERT INTO X(id,a,b,c) VALUES(ID,A,B,C)")
unlock(MyDataLayer)
(This is of course pseudocode, for SQL Server I'd need SET IDENTITY_INSERT.)

Do you think the preceding solution is equivalent to the original?
Do you know something better?

Equivalent should mean here, not necessarily generating the same ID's,
but maintaining functionality and consistence all over the database.

View 9 Replies View Related

Alternative To Using USE In A PROCEDURE

Apr 7, 2004

is there a way to get around not using USE in a PROCEDURE?

I need to because I have a main site that inserts information into other DB's that i use for various subdomains. But without being able to use USE i cant select which database is needed.

thx in advance

View 2 Replies View Related

Immediate IF ALTERNATIVE REQUIRED

May 14, 2001

Hi!

I wrote a query in MS-ACCESS using IIF. Is there any way to convert it to SQL Server Query to do the same job as it do in MS-ACCEESS

e.g. Here is manipulation with one column that I did in MS-ACCESS

IIf(InStr(1,Destinations.[Destination Name],"-",1)-1<0,Destinations.[Destination Name],Left(Destinations.[Destination Name],InStr(1,Destinations.[Destination Name],"-",1)-1)) AS COUNTRY,

View 1 Replies View Related

MSDE Alternative

Jan 26, 2004

My company develops software that is distributed to thousands of customers. We chose MSDE as the database engine. Over the past 4 months, we have spent countless hours with customers, Microsoft, Installshield and web searches trying to resolve issues with installing MSDE. The issues seem to vary by customer and most take a great deal of support time. We understood MSDE to be a product that requires little support but in hindsight, it appears that it requires a great deal of knowledge just to get installed. We make small steps but no leaps forward.

It has come time to evaluate other products. If there is a magic bullet, I would love to hear about it. In its absence, does anyone have success to share with other products?

Phil

View 13 Replies View Related

Alternative To SQLXMLBULKLOAD ?

May 2, 2008

Hi,

Just curious, is there any alternative to SQLXMLBULKLOAD for shredding and loading very large (800 megs) XML files ? Due to the nature of the XML data sent to me (which I have no control over)I am having great difficulty loading data into tables. More specifically, I can load parent data but not the child data beneath it despite using sql:relationships.

Thanks,
Jeff

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved