Re: Crosstab in SQL Server 2000 - Database Discussion Boards

Just Greeky Creek30-Jan-05 22:27

30-Jan-05 22:27

I have n databases, each one has the same tables,Sure same tables in different databases has different values. I want to join them in a just one table in dataset. For example, i have 2 databases MB0001 and MB0002 , also a table named IntegratedChequeAccounts in two . I want to join two to one.
SELECT * FROM mb0001.DBO.IntegratedChequeAccounts
, Mb0002.DBO.IntegratedChequeAccounts
In this sample IntegratedChequeAccounts tables has 3 columns and each of them has 2 row. after running "select", i get 6columnsX4row as a table. But i desire 3columnsX4rows.
Any Idea to successed in.

Re: JOIN tables in different databases

Colin Angus Mackay30-Jan-05 23:14

Colin Angus Mackay

30-Jan-05 23:14

You need to UNION the results into one dataset. What you are doing here is putting the rows side-by-side by joining the result set of one with the result set of another.

SELECT column1, column2, column3
FROM mb0001..IntegratedCheckAccounts
UNION
SELECT column1, column2, column3
FROM mb0002..IntegratedCheckAccounts

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: JOIN tables in different databases

Just Greeky Creek31-Jan-05 3:54

Just Greeky Creek

31-Jan-05 3:54

Thanks for answer but i dont know why it is not working in my way.

I have a table named "DebitsAndCredits" which has fallowing columns
AccountNumber char(12)
DebitCredit bit
Amount money

I have the same table in two databases which are MB0001 and MB0002

I would like to join them in a one result set.I write

SELECT DISTINCT (AccountNumber ) ,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit
FROM DebitsAndCreditsf Order BY AccountNumber

UNION

SELECT DISTINCT (AccountNumber ) ,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =0 AND m.AccountNumber =f.AccountNumber ) as Credit,
(SELECT SUM(Amount ) FROM DebitsAndCredits m
WHERE m.DebitCredit =1 AND m.AccountNumber =f.AccountNumber ) as Debit
FROM DebitsAndCreditsf Order BY AccountNumber

What do u offer for me?

Re: JOIN tables in different databases

Colin Angus Mackay2-Feb-05 3:04

Colin Angus Mackay

2-Feb-05 3:04

Try this:

SELECT AccountNumber, 
    SUM(CASE DebitCredit=0 THEN Amount ELSE 0 END) as Credit, 
    SUM(CASE DebitCredit=1 THEN Amount ELSE 0 END) as Debit
FROM MB0001..DebitsAndCredits
ORDER BY AccountNumber
GROUP BY AccountNumber
UNION
SELECT AccountNumber, 
    SUM(CASE DebitCredit=0 THEN Amount ELSE 0 END) as Credit, 
    SUM(CASE DebitCredit=1 THEN Amount ELSE 0 END) as Debit
FROM MB0002..DebitsAndCredits
GROUP BY AccountNumber
ORDER BY AccountNumber

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

SQL transaction rollback and commit

ppp00130-Jan-05 21:54

ppp001

30-Jan-05 21:54

Hi all,

I have a problem for data transactions across two databases.
That is one object's data is comprised of two tables from
two different databases. I can perform rollback and commit within
one database. But for this case, how can I perform rollback and commit
across two databases(for two different SQL connections) for the object ?

Thanks

Re: SQL transaction rollback and commit

David Salter31-Jan-05 7:28

David Salter

31-Jan-05 7:28

You need to use a two phase commit mechanism. What database are you using?

Database FAQ

Crosstab in SQL Server 2000

ColdWaterBlue30-Jan-05 10:51

ColdWaterBlue

30-Jan-05 10:51

Re: Crosstab in SQL Server 2000

Colin Angus Mackay30-Jan-05 12:45

Colin Angus Mackay

30-Jan-05 12:45

Gosh! I could not even follow that. I'd have to sit down with paper and pencil to work that out.

Here is an example of doing a cross tab query from the SQL Server books on-line: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_14_04j7.asp[^]

I'm guessing that both (the query you linked to and the one I've linked to) probably suffer from the same weakness. If there is a null in the source data then the value in the pivot table will be null also.

I don't know if that helps any - but the Microsoft example does at least show how to do a simple cross-tab query, even although it isn't a catch all like the one you linked to. I suggest starting with the Microsoft example and working from there.

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Need your help on SQL learning.

denniskang200430-Jan-05 7:19

denniskang2004

30-Jan-05 7:19

hi,dear buddies:

I have finished some instroductory books on SQL, but need some practice.
Do you guys have any suggestions on where I can find some good exerciese or quizs or real-world cases I can play with? OR any exercises based on the pre-installed database in SQL-server2000 (such as pub,northwind)?

Thanks a lot.
Waiting online !
Dennis

Handling quote marks in sql?

davebarkshire28-Jan-05 23:52

davebarkshire

28-Jan-05 23:52

I currently strip out any quote marks from data input (and many other characters) when sending data to the database but would like the user to be able to enter quote marks if they wish.

I use mostly stored procedures to update data but often compose sql on the fly for ad hoc searching. Can anyone tell me how I should be handling the characters which are used in T-SQL such as ' and %

Thanks in advance - Dave

Re: Handling quote marks in sql?

Colin Angus Mackay29-Jan-05 0:04

Colin Angus Mackay

29-Jan-05 0:04

davebarkshire wrote:
Can anyone tell me how I should be handling the characters which are used in T-SQL such as ' and %

Use parameterised queries.

davebarkshire wrote:
compose sql on the fly for ad hoc searching

It also sounds like you should read up on SQL Injection Attacks[^]

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: Handling quote marks in sql?

davebarkshire29-Jan-05 1:18

davebarkshire

29-Jan-05 1:18

Nothing but porn on that link.

Re: Handling quote marks in sql?

Colin Angus Mackay29-Jan-05 1:35

Colin Angus Mackay

29-Jan-05 1:35

What the....

Here is the link again: http://www.codeproject.com/useritems/SqlInjectionAttacks.asp[^]

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Reading Dataset's DataTable

ZisisGR28-Jan-05 0:22

ZisisGR

28-Jan-05 0:22

Can anyone tell me how can i read the DataTable of a Dataset..
Let's Say i used an adapter to access a dataset ,i put a query to the database (through the adapter)and the results are then stored in the Dataset's dataTable(after filling it)...How can i read the contents of the datatable(each row) and pass it to a parameter of mine..Let's Say i want to read each cell and return it to my String* Reader variable..
PLs help guys...It's an emergency..
Keep up the good work..
Thnx in advance

May it be your fate to live in interesting times

Re: Reading Dataset's DataTable

Colin Angus Mackay28-Jan-05 0:36

Colin Angus Mackay

28-Jan-05 0:36

DataTable table = myDataSet.Tables[0];
foreach(DataRow row in table.Rows)
{
    // Do what you want with each row.
}

This assumes that there is only one table in the result set.

Does this help?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: Reading Dataset's DataTable

ZisisSim28-Jan-05 3:33

ZisisSim

28-Jan-05 3:33

Thnx Colin.It does help a lot..
One more thing.I am working C++.Net and i want to organise the Connection to the database and the results of a specific SQL Statement through the adapter and the table in a single class... TO BE MORE SPECIFIC
#pragma once

class DBConn
{
public:
DBConn(void);
~DBConn(void);
public:
String* Type;
int Cost;
public:
String* FigureUserType(String* SenderNo)
{
SqlConnection * myConn = new SqlConnection(S"data source=(local);",S"integrated security=true;initial catalog=SMSCenter");
myConn->Open();

SqlDataAdapter * daResults = new SqlDataAdapter();

daResults->SelectCommand=new SqlCommand("SELECT Type FROM Subscribers WHERE PhoneNumber=@SenderNo",myConn);

SqlParameter* SenderParam=new SqlParameter();

//Set Parameter Properties
SenderParam = daResults->SelectCommand->Parameters->Add(
new SqlParameter("@SenderNo", SqlDbType::VarChar));
SenderParam->Value=SenderNo;
SenderParam->SourceVersion = DataRowVersion::Current;

//Create The Dataset to Fill
DataSet * dsresults = new DataSet();
//Fill the DataSet
daResults->Fill(dsresults,"Apotelesmata")

//Create The datarow
DataRow* drResults=new DataRow();

for(int i=0;>= dsResults->Tables->Item ["Apotelesmata"]->Rows->Count;i++)
{
myAuthorRow = myDataSet->Tables->Item["Apotelesmata"]->Rows->Item[i];

String* RetType=myAuthorRow->Item ["SenderNo"]->ToString ());

}

return RetType // Suppose we have only one Result

};

But I don't know where in the class i should declare the following...
using namespace System::Data; // Generic ADO.NET definitions, such as DataSet
using namespace System::Data::SqlClient; // SQL Server data provider definition
namespace system and so goes on...
PLS HELP

May it be your fate to live in interesting times

Convert SqlDataReader to DataSet

ILoveSing27-Jan-05 22:47

ILoveSing

27-Jan-05 22:47

How can I do it? Please tell me.
Thank you

Nothing

Re: Convert SqlDataReader to DataSet

Colin Angus Mackay27-Jan-05 22:51

Colin Angus Mackay

27-Jan-05 22:51

Why don't you just use a data adapter to fill the dataset in the first place?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Determining the ID of an inserted row

Ahmed Hefny27-Jan-05 21:26

Ahmed Hefny

27-Jan-05 21:26

I am using ADO.NET to access a SQL Server 2000 database in which there is a table Orders whose primary Key 'OID' is incremented automatically, so when I insert a new order I don't specify its OID. However, I need to know the OID of the inserted order to add items to it. Isn't there a function or something to do that ? Thank you.

Ahmed Said Hefny
Computer Engineering Student
Cairo University

Re: Determining the ID of an inserted row

Colin Angus Mackay27-Jan-05 22:58

Colin Angus Mackay

27-Jan-05 22:58

You can use IDENT_CURRENT('table_name') to get the last automatically generated value inserted into the table that you specify.

Probably the best way to do this is to create a stored procedure to do the increment and then get the value

CREATE PROCEDURE InsertOrder
    -- Set parameters for the values to be inserted here
AS
    INSERT INTO Orders( column_names )
    VALUES ( parameters containing the data to be inserted );

    SELECT IDENT_CURRENT('Orders');
GO

Then in your .NET application you can use the SqlCommand.ExecuteScalar() to get the value out.

Does this help?

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

saving and retrieving image data

Rashid_Mehmood27-Jan-05 20:01

Rashid_Mehmood

27-Jan-05 20:01

Hi All,
I'm working on a c# application that suppose to store and retrieve images in the sql server database. By choosing a column of type image, it's size is defaulted to 16bytes but images usually are larger than that. How can I increase the size of an image column so that it can hold images of some 10+kb? Plz answer and oblige.
Thanks

Re: saving and retrieving image data

Colin Angus Mackay28-Jan-05 0:39

Colin Angus Mackay

28-Jan-05 0:39

Rashid_Mehmood wrote:
it's size is defaulted to 16bytes

That is the size of the pointer to the image, not the image.

Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums

Re: saving and retrieving image data

Mike Dimmick28-Jan-05 2:38

Mike Dimmick

28-Jan-05 2:38

An image column can hold up to 2GB of data, IIRC. As Colin says, the 16 byte value is the pointer to where the data actually lives. Image data is not held on the same data page as the rest of the row - you should be aware that more I/O will occur if you use text, ntext or image columns.

Having said that SQL Server has a feature 'text in row' which allows you to specify that up to a certain amount of data will be placed in the row itself, not on separate pages.

Stability. What an interesting concept. -- Chris Maunder

Re: saving and retrieving image data

Mr.Cooper9-Feb-05 1:01

Mr.Cooper

9-Feb-05 1:01

As a work around u can save the path to the images which will reduce the size of the db and also reduces the effort to handle images in sql.

i hope this helps.

datatable only working occasionally

lxhan27-Jan-05 18:49

lxhan

27-Jan-05 18:49

I have a VB.Net windows app that retrieves data from 3 different tables. The SQL command uses a 3 table join. The results from the query is filled into a datatable and later populated into a combobox.

The SQL uses the values from a date field inside a text field, for example, 01-15-2005. So, each time I submit the subroutine it executes the SQL command and VB.Net code to retrieve the required data. The date field changes according to the user’s needs.

There’s no consistency when the error pops up.
The error message I’m getting when it executes, da.Fill(dataTable), “There’s no row at position 0”,. The information I’m on the stack track is as follow: Calling SQLDisconnect --- OleDB UnInitialize not called!!!
Connection Object destroyed

If I copy the sql into a sql query tool it does retrieve the required data according to the date field.

Here’s a sample of the subroutine:

cmdQueryINVOICE = _
"SELECT PAYITEM.TRANSACT_NUMBER, " _
& "PAYITEM.ACCOUNT_NUMBER, " _
& "PAYITEM.PAY_DATE, " _
& "LSEINVH.INVOICE_NUMBER " _
& "FROM LSEINVH, PAYITEM, TRANSHDR " _
& "WHERE " _
& " LSEINVH.INVOICE_NUMBER > '" & 75000 _
& "' AND LSEINVH.STATUS <> '" & sStatus _
& "' AND LSEINVH.INVOICE_NUMBER = TRANSHDR.INVOICE_NUMBER " _
& " AND PAYITEM.PAY_DATE > '" & InvoiceDates.StartDate _
& "' AND PAYITEM.PAY_DATE < '" & InvoiceDates.EndDate _
& "' AND PAYITEM.COMPANY = '" & sCompany _
& "' AND PAYITEM.TRANSACT_NUMBER = TRANSHDR.TRANS_NUMBER order by PAYITEM.PAY_DATE desc "

Dim sqlQuery As OleDbCommand = connDf.CreateCommand
Dim dtInvDate As New DataTable()
Dim iInvCnt, x As Integer
Dim sUnitQuery As String
Dim daInv As New OleDbDataAdapter()

Try

daInv = New OleDbDataAdapter(cmdQueryINVOICE, connDf)
daInv.Fill(dtInvDate)

Dim LoopRow As Data.DataRow
Dim test As String = dtInvDate.Rows.Item(0).ToString

For Each LoopRow In dtInvDate.Rows
cboInvoice.Items.Add(LoopRow.Item("INVOICE_NUMBER").ToString + " " + LoopRow.Item("PAY_DATE"))
Next

Catch e As Exception
Dim errMsg As String = e.Message

Finally
connDf.Close()

End Try

Thanks, for any help!

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.