MS SQL Server :: Repated Colums Returned

Data Returned From .WriteXML Is Different Than What Is Returned In Query Analyzer.

Jun 20, 2006

I have a strange problem. I have some code that executes a sql query. If I run the query in SQL server query analyzer, I get a set of data returned for me as expected. This is the query listed on lines 3 and 4. I just manually type it into query analyzer.
Yet when I run the same query in my code, the result set is slightly different because it is missing some data. I am confused as to what is going on here. Basically to examine the sql result set returned, I write it out to an XML file. (See line 16).
Why the data returned is different, I have no idea. Also writing it out to an XML file is the only way I can look at the data. Otherwise looking at it in the debugger is impossible, with the hundreds of tree nodes returned.
If someone is able to help me figure this out, I would appreciate it.
1. public DataSet GetMarketList(string region, string marketRegion)2. {3. string sql = @"SELECT a.RealEstMarket FROM MarketMap a, RegionMap b " + 4."WHERE a.RegionCode = b.RegionCode"; 5. DataSet dsMarketList = new DataSet();6. SqlConnection sqlConn = new SqlConnection(intranetConnStr); 7. SqlCommand cmd = new SqlCommand(sql,sqlConn);8. sqlConn.Open();9. SqlDataAdapter adapter = new SqlDataAdapter(cmd); 10. try11. {12. adapter.Fill(dsMarketList);
13. String bling = adapter.SelectCommand.CommandText;//BRG 14. dsMarketList.DataSetName="RegionMarket"; 15. dsMarketList.Tables[0].TableName = "MarketList"; 16. dsMarketList.WriteXml(Server.MapPath ("myXMLFile.xml" )); // The data written to 17. myXMLFile.xml is not the same data that is returned when I run the query on line 3&4 18. // from the SQL query 19. } 20. catch(Exception e) 21. { 22. // Handle the exception (Code not shown)

View 2 Replies View Related

Rows As Colums

May 8, 2007

I know this is possible in DB2 and Oracle, but what about for SQL-server 2005

1)      select X number of rows from table1
2)      I need colums for each row of table1 in a new table
3)      As such, Select (select * from X where x.id = @ID), a,b,c    from table Y where y.Id = @ID
And I dont want to use IfExists.
Thanks
DK

View 1 Replies View Related

Query Across All Colums

May 18, 2004

Hi folks. This is my first post around here.
So I want to query a select on a table. Is there a simpler way to match the where clause with all columns than referencing every single column in the where clause?

Thanks

Shabassa

View 12 Replies View Related

Updating Colums

Nov 20, 2006

Hello all.

I have a column named 'RELATED' of type text in a table. I want to update the contents of this colums...........but still keep what is currently stored in it. Basically i want to add some text onto the end of the string it currently contains.

Any help would be much appreciated.

View 1 Replies View Related

Pivoting Row Values Into Colums

Oct 11, 2004

I’d like to get some data which includes month values bound to a data grid. The data is stored in a table like so:

Measure Month Value
A June 10.00
A July 9.00
A Aug 11.00
B Jun 100.00
B Jul 98.00
B Aug 99.00
C Jun 0.75
C Jul 0.8
C Aug 0.91

I need to report the data like this:
Measure Jun Jul August
A 10 9 11
B 100 98 99
C 75% 80% 91%

This was simple in classic ASP. Just use two recordsets, create a new table cell for each month using the first recordset then use the second recordset for each row.

But is there a way to “Pivot�? or rotate the data so I can use the DataGrid? It only seems possible if each month has its own column field in table. Each month add a new column.

I can restructure the database, if needed.

I thought about creating a Cube, but that seems to have its own limitations. For example what if I want to add a Column for Quarter and year totals? I don’t think it’s possible to show multiple planes like that in an query of a cube.

It seems that this might be resolved in the presentation layer or the data layer. Any Suggestions?

View 1 Replies View Related

Convert Rows To Colums

Aug 26, 2004

I am getting this from my client
TableA
StorID LineNumber Text
30000 1 ClientName
30000 2 ClientSurname
30000 3 3333333
30000 4 20-05-2004

I actually want this to look like
TableB
StoryID Name Surname Policy Date
30000 Name Surname 3333333 20-05-2004

Can you help with the script to convert this to one record with many fields (as in TableB) if this is

View 4 Replies View Related

Bcp Command To Give Colums

Jan 8, 2004

Can someonte tell me that if i
bcp bda..mytable out c:discounts.xls -c -p , how can I put the first row as my column names since I get only data

View 2 Replies View Related

Validating And Updating Colums

Jul 23, 2005

Hi all,I am a newbie to sql and I need your help.I want to update column (email) from one table to another validating theCustomerid column in both table. Update the email address in productiontable with the email address in temp table if the customerid is same in bothtables.What would be the query?Thanks,

View 3 Replies View Related

Matrix Subtotal But Not All Colums

Feb 21, 2007

Hello,

I have this problem that I do not now how to solve it:

I have a report (matrix) where in the lines I have an order status, while in the columns I have the number of orders, the number of lines and the average time of delivery (calculated measure on as 2005 cube).

If put the subtotals the system will add the number of orders, the number of lines but instead of doing the average time of delivery, it will sum all the average of each order status.

Nr. Of Orders Nr. of Lines Average Delivery Time

Open Order 10 20 3.2

Closed Order 15 30 5

Total 25 50 8.2 (it should be, for example, 4.5)

Is there a way in which I can tell the system to not calculate the total for the third column?

If I use excel there is no problem because it will use the server formatting of AS2005 while Reporting Services is not doing it.

Thanks to all!

Andrea

View 4 Replies View Related

Joined View Does Not Contain All Colums

Jul 20, 2006

The joined view is named "dbo.viewExecView" and is like:

SELECT Bank_No, data_center
FROM [ALPHASQL2000].ev_db.dbo.Bank

The new view that joins to the above view is like:

SELECT bank.BankID, evBank.data_center AS DataCenterID
FROM dbo.Bank AS bank INNER JOIN
dbo.viewExecView_Bank AS evBank ON bank.BankID = evBank.BankID WHERE (bank.InactiveDate IS NULL)

Note: The data_center column (an int) was recently added to the Bank table in the linked ev_db database and it shows up there. It also shows up in the view "dbo.viewExecView". It does not appear in the new view that joins to "dbo.viewExecView". And when I run the 'new' view, I get an Error Message: Invalid column name 'data_center'.

I've tried to simplify this as much as possible while still including the pertinent information. Any help very much appreciated, I am currently stumped.

Regards,

Joe

View 4 Replies View Related

INNER JOIN: Joining Two Different Colums With One Table?

Nov 15, 2004

Hello everyone,

I'm stuck on something here. Any help would be great. This is a relational database question.

I'm trying to inner join two columns of one table with one column of another. The follwoing code doesn't work, but I think you can see what I'm trying to do.

Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_SubCategories.SubCategoryName,
_SubCategories.SubCategoryName,
LinkName
FROM
_Links
INNER JOIN
_SubCategories ON _Links.CategoryId = _SubCategories.SubCategoryId
INNER JOIN
_SubCategories ON _Links.StatusId = _SubCategories.SubCategoryId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable

Also, I know how to do this if I had seperate category tables for each category (LinkCategory, LinkStatus). For examlple:

Procedure _Links_List
AS
CREATE TABLE #TempTable
(
LinkId int,
LinkCategory varchar(50),
LinkStatus varchar(50),
LinkName varchar(50)
)
INSERT INTO #TempTable
(
LinkId,
LinkCategory,
LinkStatus,
LinkName
)
SELECT
LinkId,
_Links_Categories.CategoryName,
_Links_StatusCategories.StatusName,
LinkName
FROM
_Links
INNER JOIN
_Links_Categories ON _Links.CategoryId = _Links_Categories.CategoryId
INNER JOIN
_Links_StatusCategories ON _Links.StatusId = _Links_StatusCategories.StatustId
SELECT
LinkId,
LinkCategory,
LinkStatus,
LinkName
FROM
#TempTable

I know the above works but I'm trying to figure out how to have just one category table and one subcategory table for all of my categories of all my tables.

Table_Categories: CategoryId (Primary Key), CategoryName

Table_SubCategories: CategoryId, SubCategoryId (Primary Key), SubCategoryName

So instead of having to create a new table for every category and all the procedures for them for all my tables, I want to be able to just use these two tables.

If anyone knows how I go about this, especially when a table uses two category columns, I Thank you.

Alec

View 3 Replies View Related

Variable Colums In Stacked Table

Dec 14, 2007

Hello, and thanks for taking the time to read this.

NOOB question:

In dealing with, say, shirts -- I have a DB that serves as a template for several customers. Each customer may have different ranges of sizes (one may have S,M,L and the other might also have XL,XXL). So the CATALOG table is:

CREATE TABLE [dbo].[T_Catalog](
[StlyeID] [int]
) ON [PRIMARY]
with data:
1
2

the SIZES table (filled in by the customer with all the size ranges they carry) is:

CREATE TABLE [dbo].[T_Sizes](
[SizeID] [int],
[SizeName] [nchar](10)
) ON [PRIMARY]
with data:

1,Small
2,Medium
3,Large
4,Xtra-Large

and the AVAILSIZES table would be:

CREATE TABLE [dbo].[T_AvailSizes](
[StyleID] [int],
[SizeID] [int]
) ON [PRIMARY]

1,1
1,2
1,3
2,1
2,3
2,4

Basically, then, we know that:

style 1 comes in Small, Medium, and Large
style 2 xomes in Small, Large, and Xtra large

WE know that, but getting SQL to tell us that is a major PIA!!

Now,

SELECT
t_Catalog.StyleId, t_AvailSizes.SizeID
FROM
t_Catalog
INNER JOIN t_AvailSizes ON
t_Catalog.StyleId = t_AvailSizes.StyleId

will give me a nice list of each item number with a separate row for each size number.

My questions are:

How do I get the size NAMES?

How would I get all of the sizes into a single row, so that there is a single row for each catalog StyleID (that's all I ever wanted to begin with)?

Is this the right way of doing this?

In reality I have about six columns that can contain multiple and variable items like this, and when trying to even think about resolving it all into a single record my brain tries desparately to crawl out my left ear.

Thanks for any help and information you can provide.

View 2 Replies View Related

I Want To Join Two Colums From Two Differet Database

May 1, 2008

Hello I have a Source database and a Target database.

I want to join one table from the source to the other table in the target.

Please can some one write a sql query for this.

i gues its something like

select tablesource.col,tabledest.col
from database..tablesource,database..tabledestination

Ok One more question is where do I execute this Query in which database.. IF at all its possible to this.

View 6 Replies View Related

Order By Colums Asc Does Not Work In SQL SERVER 2005

Feb 12, 2008

Hello

Iam usng sql server 2005 database

I had this table

CREATE TABLE [dbo].[Companies](
[CompanyID] [smallint] IDENTITY(1,1) NOT NULL,
[Company] [nvarchar](40) NOT NULL,

I entered Arabic Names

so I write this query
select * from companies order by company asc
--and this one
select * from companies order by company

and I try to order desc

but the row didnt appear out ordered

and the big prtoblem happened when I try to update a company Its go to the last row when it appears

please help me

thanks

View 9 Replies View Related

How To Dynamic Specify The Colums In Full-Text Search?

May 14, 2004

My problem is simple: i want to dynamic specified the columns in the ContainsTable, this is possible? Please see the example.

Declare @Test1 int
Declare @Test2 int
Declare @Query varchar(50)
Declare @Temp varcahr(50)

--Test
Set @Test1=1
Set @Test1=0
Set @Query='something'

--Add the column to put in containstable
IF (@Test1=1)
Begin
Set @Temp='ID'
End

IF (@Test2=1)
Begin
Set @Temp= @Temp + ',Name'
End

SELECT *
FROM
<table>
INNER JOIN
CONTAINSTABLE (<table>, @Temp, @Query) AS KEY_TBL
ON <table>.ID = KEY_TBL.[KEY]

Thanks

View 1 Replies View Related

Showing Colums And Rounding 2 Decmial Places

May 8, 2007

Okay I have a problem and I have no idea to do it. I need the SQL syntax to come up with this:
the formatting is messed up though

a query to display the department name, location name, number of employees, and the average salary for all employees in that department. Label the columns dname, loc, Number of People, and Salary, respectively. Round the average salary to two decimal places.

DNAMELOCNumber of PeopleSalary
-------------------------------------------------------------------
ACCOUNTINGNEW YORK3 2916.67
RESEARCHDALLAS52175
SALESCHICAGO61566.67

View 2 Replies View Related

Auto Fill In Colums Trough Foreign Key Relationship

Jun 10, 2004

Hi,

I have a table users where there is a user_id and an department column.
Also i have a table called KRS where there are the same columns, when a userid is given i want to auto fill in the departmentid,

Can someone help me with this?

Cheers Wim

View 2 Replies View Related

Is There A Way We Can Implement Business Logic By Storing It In Colums Of A Table And Calling It?

Jul 20, 2005

What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg

View 1 Replies View Related

Multiple-step OLE DB Operation Generated Errors. Adding Colums Using ADOX

Nov 26, 2007

Hi
I'm trying to add columns to an existing table using ADOX. I'm using C++/CLI. My code is as follow

Table->default->Append("new_column", ADOX:: DataTypeEnum::adWChar, 255);
Table->default["prev_instance_id"]->Properties["Jet OLEDB:Compressed UNICODE Strings"]->default = true;

The first line of code works fine but when I try to set the value of the property I get the following excpetion:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in MyDll.dll
Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Any idea?

Marco

View 7 Replies View Related

How To Copy A Column(or Colums) From A Table In One Database To Another Table In A Different Databas

Oct 1, 2001

How do I copy a column(or colums) from a table in one database to another table in a different database

View 1 Replies View Related

Cannot See The Colums In The Design View Of Queries SQL 2000 And MSAccess 2000(adp)

Nov 21, 2006

Cannot see the Colums in the "design view" of Queries. All i see when i want to design a new query is *columns

This happens in only one database, in other databases using same server i can see the colums and can tick them to view then in the query.

In enterprise manager i see all the columns.

Using SQL 2000 and MSAccess 2000

View 1 Replies View Related

One Row Returned But E.AffectedRows ==0?

Jan 9, 2007

Stored Procedure:CREATE PROCEDURE [dbo].[GetBanner] @BannerPage nvarchar(50), @MagazineID intASBEGIN SET NOCOUNT ON; DECLARE @BannerID int SELECT TOP 1 @BannerID = BannerID FROM Banners WHERE BannerPage=@BannerPage AND MagazineID = @MagazineID AND StartDate <= GetDate() AND EndDate >=GetDate() ORDER BY Views SELECT BannerID, BannerFileName, AltText, URL, Views FROM Banners WHERE BannerID = @BannerID UPDATE Banners SET Views = Views +1 WHERE BannerID = @BannerID UPDATE BannerStats SET Views = Views + 1 WHERE BannerID = @BannerIDEND OnSelected method: protected void dsBanner_Selected(object sender, SqlDataSourceStatusEventArgs e) { int RecordCount = e.AffectedRows; Response.Write(RecordCount); } This ALWAYS gives me 0 - despite the fact that the correct banner is pulled from the database, and updated etc. Also, when I execute the procedure (for valid parameter values) in SSMS, I get "1 row(s) Affected". Can anyone tell me what I need to change to get the proper value? Thanks

View 6 Replies View Related

Name Column Returned

Sep 27, 2007

I have the following sql:SELECT courseModuleCode FROM courses WHERE courseCode like '%' + @prefixText + '%'UNION
SELECT courseName FROM courses WHERE courseName like '%' + @prefixText + '%'
UNIONSELECT TeacherName FROM courses WHERE TeacherName like '%' + @prefixText + '%' it returns one column, i want to name tha column as "words"how do i do it?thanks

View 3 Replies View Related

No Record Set Returned

Sep 10, 2004

I have a table that COULD be linked to 3 other tables, but if one of the link fields is zero I get nothing back at all. Here is my query:
SELECTEvents.*, Agencies.A_NAME AS A_NAME,
(Families.F_FNAME + ' ' + Families.F_LNAME) AS F_NAME,
(Candidates.C_FNAME + ' ' + Candidates.C_LNAME) AS C_NAME
FROM Events
INNER JOIN Agencies ON Events.E_AGENCY_ID = Agencies.A_ID
INNER JOIN Families ON Events.E_FAMILY_ID = Families.F_ID
INNER JOIN Candidates ON Events.E_CANDIDATE_ID = Candidates.C_ID
WHEREE_ID = 89

Even if all the inner join id's are zero, I would at least like to get back the event information.
Could anyone help me out here, or do I have to do 4 queries?

Thanks in advance.

View 2 Replies View Related

Once Again, No Records Returned....

Dec 21, 2004

The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?

----------------------------------------------------------------------------------------------------
CREATE PROCEDURE spUnitsbyUnitID
@unitid int
AS

SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled

FROM tbl_units as A

INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
INNER JOIN tbl_contacts as D on A.unitid = D.unitid
INNER JOIN tbl_cameras as E on A.unitid = E.unitid

WHERE A.UnitID = @unitID
GO
-----------------------------------------------------------------------------------------------------

View 3 Replies View Related

Subquery Returned More Than 1 Value...

Mar 9, 2006

can anyone tell me why i get the following error when i run the below query? its a simple query. i dont understand why its throwing an error. Thanks in advance.

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

update purchaseorders set orderstatusID = 4 where purchaseorderid in (
select distinct postprocesspurchaseorderID from wishlistitems where postprocesspurchaseorderID is not null
)

View 1 Replies View Related

Different No. Of Rows Returned In SEM Vs QA

Nov 19, 2004

Hi,

Any idea why when I run a SELECT stament in Query anaylser it returns 45 rows. But when I create the exact same SQL as a view in Enterprise manager it only returns 44 rows?

Thanks,

Alph

View 3 Replies View Related

Subquery Returned More Than 1 Value

Dec 7, 2011

I am getting the below error while trying to run sql query

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

but if in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error.

SELECT substring(sc.container_id,0,14) container_id,
imc.item_class,
sh.ship_to, sh.shipment_id,
cust.ADDRESS1,
sh.carrier,

[code]...

View 3 Replies View Related

Exclude If Returned Value Is 0

Jan 27, 2012

I am running a query that works just fine however, I would like it to exclude value that are equal to zero.

Basically my query looks at the commission that clients pay over a number of periods.

So it goes like this

T.Client_Code as Client
,SUM(CASE t.Transaction_Date WHEN DATEADD(day, DATEDIFF(day, 1, GETDATE()),0)THEN (ABS (t.transaction_commission) /((fx.Exchange_Bid + fx.Exchange_Ask)/2 )) ELSE 0 END)as Commission_Day

FROM TABLE T
JOINING FX TABLE

WHERE
fx.Currency = 'USD'

And T.Salesman_Name in ('X''Y'Z)

Group BY
T.Client_Code

It works perfectly fine however, we dont transact with our clients everyday so therefore this list will return all of our clients in the database and many will have generate zero commission. I want to keep the query along those lines I just need to insert something that says "ONLY SHOW WHEN Commission is not ZERO.

View 2 Replies View Related

Subquery Returned More Than 1 Value?

Mar 25, 2015

i am trying to run Select query to get "application name" and "role" for each user in the DB problem is that each user has more than one App that he is useing and for each App he can have more than one role

My code is:

Code:
select top 100
username,
(Select b.name
from application b
inner join userroleapplication c
on b.applicationID = c.applicationID ) Application,
(select name from role d, userroleapplication gr
where gr.roleID = d.roleID and gr.userID = a.userID) role
from users a
where username in
(
'username'
)

i know where is the problem but i cant figure how to fix this

What i need as a output

Username | Application | Role

"user1" | "App1" | "Role1"
"user1" | "App1" | "Role2"
"user1" | "App2" | "Role1"
"user1" | "App3" | "Role1"
"user2" | "App1" | "Role1"
"user2" | "App3" | "Role1"
etc.

View 2 Replies View Related

Subquery Returned More Than 1 Value?

Apr 15, 2008

Hi

I have two tables and I need write a query that include subquery that return more than 1 value,Can you give me a idea how to write the query

The table with data is like this,one file maybe belong to many group.

Filename FilegroupID

file1 1
file2 2
file1 3
file1 4
file3 1
file2 5

I need write a query to get the result like this

file1 1,3,4
file2 2,5
file3 1

I try to write a query with subquery but I get error message

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"

I use SQL Server 2005.but I need the subquery return data like "1,3,4".

Please help me.

Thanks

Mark

View 7 Replies View Related

Why Are Nulls Not Being Returned?

Apr 29, 2008

Select * from table where field <> 'This'

Why does that not return nulls? 'This' does not equal null. Why does sql server work this way?

View 2 Replies View Related