Regarding dblib

hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer

Try some SQL Engine specific forum, e.g. http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

This forum is for Integration Services component, not sure if there are appropriate people to respond to this question here.

sql

Regarding dblib

hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer
You shouldn't really invest any more time in DBLib, and move to some other
data access APIs (ADO.NET, OLEDB, ODBC, etc). You might be talking about
SQL2000, but consider what SQL2005 BOL says about Db-Library:
"Although the SQL Server 2005 Database Engine still supports connections
from existing applications using the DB-Library and Embedded SQL APIs, it
does not include the files or documentation needed to do programming work on
applications that use these APIs." on page
http://msdn2.microsoft.com/en-us/library/ms143729(en-US,SQL.90).aspx
Linchi
<umer.zafar@.gmail.com> wrote in message
news:1131628582.386464.75780@.g47g2000cwa.googlegro ups.com...
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
> Thanks in advance
> Umer
>

Regarding dblib

hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
UmerTry running the SQL Profiler to trace the database activity.|||(umer.zafar@.gmail.com) writes:
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
Difficult to understand what you mean. One very nice feature of DB-Library
is that it does not do things behind your back like those modern API are
fond of doing.
But, alas, Microsoft has put DB-Library on the deprecation list, and
DB-Library does not have support for features added to SQL 7 and later.
While you still connect to SQL 2005 with DB-Library, SQL 2005 does not
come with any files to support DB-Library. A future version of SQL Server
will not support DB-Library at all.
Thus, unless you have some legacy application to maintain, you are best
off staying away from DB-Library.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks..helping to get me my answer

Regarding dblib

hi..I am running DBLIB (sample application of SQL Server)
between 2k and 2k3. Well seems working perfect..but want
to know whats going on in the Database..I think some
DB configuration (like DB connection or Table creation)
is required to see the effect of executed/commited
transactions..need some guidence.
Thanks in advance
Umer
Hi
Run SQL Server Profiler to see what is being sent to SQL Server.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<umer.zafar@.gmail.com> wrote in message
news:1131628639.794052.79080@.g47g2000cwa.googlegro ups.com...
> hi..I am running DBLIB (sample application of SQL Server)
> between 2k and 2k3. Well seems working perfect..but want
> to know whats going on in the Database..I think some
> DB configuration (like DB connection or Table creation)
> is required to see the effect of executed/commited
> transactions..need some guidence.
> Thanks in advance
> Umer
>
|||Hi..Thanks a lot ..answer was very helping.
Regards
Umer

Regarding Aggregate conditions ..

Hai frns small help needed.

I have a table called sample and i have the following requirement. i.e i need sum(credit) group by ssn no.

One special condition is as follows:

For each distinct ssn if "flag" has the same CX value,then out of all the records with the same CX value, the highest "credit" value is added to the sum for that "ssn" and the rest are ignored.
If while adding "credit" to the sum and if "credit" value is equal to zero then "sum" value is used for summing else "credit" value is used.
Can any one help me out in trying this logic. I have tried but i could'nt able embed the conditions inbetween the Sql statetment.

Here is the query is used

select * from sample

id ssn credit flag sem
1 101 0 C9 0
2 101 4 C9 3
3 101 4.5 C9 2
4 101 3.5 C1 1
5 102 4.2 C3 3
6 103 0 C1 2

select ssn,flag,sum(case credit when 0 then sem else credit end) as sum from sam2
group by ssn,flag

ssn flag sum_val
101 C1 3.5
103 C1 2.0
102 C3 4.2
101 C9 8.5

The above output is wrong one.

Expected output

101 4.5+3.5=8.0
102 4.2
103 2.0

Any help would be appreciated

Regards,SELECT ssn
, SUM(max_credit)
FROM --MAX credit\ sem per ssn & flag
(SELECT dbo.my_table.ssn
, max_credit = MAX(CASE WHEN credit = 0 THEN sem ELSE credit END)
FROM dbo.my_table
GROUP BY dbo.my_table.ssn
, dbo.my_table.flag) AS mc
GROUP BY ssn|||Actually I think that is flawed. I don't think your sample data is comprehensive enough.|||Thanks, Here is more sample data

101 0 C9 0
101 4 C9 3
101 4.5 C9 2
101 0 C9 2
101 3.5 C1 1
101 3.5 C2 2
104 3.5 C1 3
105 3.5 C2 0
106 3.5 C3 1
107 3.5 C4 1
109 3.5 C6 4
110 3.5 C7 1

Regards,|||SELECT ssn
, SUM(case when max_credit = 0
then sem_for_max_credit
else max_credit end) as daSum
FROM (
SELECT ssn
, flag
, sem as sem_for_max_credit
, credit as max_credit
FROM daTable as T
WHERE credit =
( SELECT MAX(credit)
FROM daTable
WHERE ssn = T.ssn
AND flag = T.flag )
) AS maxes
GROUP
BY ssn|||Thanks for the help.sql

Regarding a sample query

Hello,
I am a very complex problem in front of me. Kindly help me out

in acheiving the same.

Say I have a table called InfoName with two columns Name and ID

InfoName

Name ID

OS 1
SP 2
Driver 3
fasdf **
** ***
** ****

(I AM INTERESTED IN ONLY FIRST THREE ROWS )

I have another table Infotxt which uses the ID of InfoName as

foreign key. It stores the value of this ID as shown

InFotxt

ID Value UnitNAME

1 Win 2000 raj
2 SP 4 raj
3 40 GB raj

1 Win xp jay
2 SP 2 jay
3 20 GB jay

NOw I need to present it with unitname's configuration of OS,

Sp and disk capacity like below.

name OS SP Drive
Raj win2000 sp4 40 GB
Jay winxp sp2 2o GB

That is, the rows of the InfoName table (first 3 rows) should

be the columns of my resultant query.

How can I achieve the same.
Please give me some ideas, and if the question is silly, I am

very sorry, because I am new to database queries...

Thanks,
cspek

cspek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Try this:

SELECT unitname,
MIN(CASE WHEN id = 1 THEN value END) AS os,
MIN(CASE WHEN id = 2 THEN value END) AS sp,
MIN(CASE WHEN id = 3 THEN value END) AS drive
FROM InfoText
WHERE id BETWEEN 1 AND 3
GROUP BY unitname

You have to be more specific than "first three rows". Understand that
tables in SQL are not ordered. There is no fixed concept of a first,
second or Nth row.

This is called a cross-tab report. There are other solutions for
producing cross-tabs dynamically in SQL Server but many people would
say that you should do this instead in your client application or
reporting tool. See:

http://www.aspfaq.com/show.asp?id=2462

--
David Portas
SQL Server MVP
--|||Hello,
Thanks...Wil look into it...

cspek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

reg. ReportService2005.asmx.

I am new to Reporting Service, I got the sample code, there is a method used
to connect reporting service,
http://servername/reports/ReportService2005.asmx,
But i don't find ReportService2005.asmx.
I chekced http://servername/reports/ folder ts is not there, how to get this
file? Is there any separate installtion/configuration needed?
I searched web, i don't find any information regarding this.
pl. any help
thanks
KalYou need to use http://servername/ReportServer/ReportService2005.asmx
/Reports is the web interface for Reporting Services whereas /ReportServer
is the Web Service interface.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: reg. ReportService2005.asmx.
> From: =?Utf-8?B?S2FseWFu?= <Kalyan@.discussions.microsoft.com>
> Subject: reg. ReportService2005.asmx.
> Date: Mon, 8 Oct 2007 06:02:17 -0700
> I am new to Reporting Service, I got the sample code, there is a method
used
> to connect reporting service,
> http://servername/reports/ReportService2005.asmx,
> But i don't find ReportService2005.asmx.
> I chekced http://servername/reports/ folder ts is not there, how to get
this
> file? Is there any separate installtion/configuration needed?
> I searched web, i don't find any information regarding this.
>
> pl. any help
>
> thanks
> Kal
>|||Thanks Chris,
"Chris Alton [MSFT]" wrote:
> You need to use http://servername/ReportServer/ReportService2005.asmx
> /Reports is the web interface for Reporting Services whereas /ReportServer
> is the Web Service interface.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> > Thread-Topic: reg. ReportService2005.asmx.
> > From: =?Utf-8?B?S2FseWFu?= <Kalyan@.discussions.microsoft.com>
> > Subject: reg. ReportService2005.asmx.
> > Date: Mon, 8 Oct 2007 06:02:17 -0700
> >
> > I am new to Reporting Service, I got the sample code, there is a method
> used
> > to connect reporting service,
> >
> > http://servername/reports/ReportService2005.asmx,
> >
> > But i don't find ReportService2005.asmx.
> >
> > I chekced http://servername/reports/ folder ts is not there, how to get
> this
> > file? Is there any separate installtion/configuration needed?
> >
> > I searched web, i don't find any information regarding this.
> >
> >
> > pl. any help
> >
> >
> > thanks
> > Kal
> >
>|||Not a problem :)
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: reg. ReportService2005.asmx.
> From: =?Utf-8?B?S2FseWFu?= <Kalyan@.discussions.microsoft.com>
> References: <5D2E4DAA-6C21-4138-89A1-4109357A14BA@.microsoft.com>
<7snSqEbCIHA.360@.TK2MSFTNGHUB02.phx.gbl>
> Subject: RE: reg. ReportService2005.asmx.
> Date: Mon, 8 Oct 2007 06:54:01 -0700
> Thanks Chris,
>
> "Chris Alton [MSFT]" wrote:
> > You need to use http://servername/ReportServer/ReportService2005.asmx
> >
> > /Reports is the web interface for Reporting Services whereas
/ReportServer
> > is the Web Service interface.
> >
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> > > Thread-Topic: reg. ReportService2005.asmx.
> > > From: =?Utf-8?B?S2FseWFu?= <Kalyan@.discussions.microsoft.com>
> > > Subject: reg. ReportService2005.asmx.
> > > Date: Mon, 8 Oct 2007 06:02:17 -0700
> > >
> > > I am new to Reporting Service, I got the sample code, there is a
method
> > used
> > > to connect reporting service,
> > >
> > > http://servername/reports/ReportService2005.asmx,
> > >
> > > But i don't find ReportService2005.asmx.
> > >
> > > I chekced http://servername/reports/ folder ts is not there, how to
get
> > this
> > > file? Is there any separate installtion/configuration needed?
> > >
> > > I searched web, i don't find any information regarding this.
> > >
> > >
> > > pl. any help
> > >
> > >
> > > thanks
> > > Kal
> > >
> >
> >
>

Refining MS ACCESS data to csv file using T-SQL

Hello,

I have a table with these sample data pattern:
Col1 Col2
001 2
002 1
002 2
002 1
003 4
003 4
003 3
003 3
003 5

The expected output in the csv file should be exactly like this:
001,2
002,4
002,
002,
003,19
003,
003,
003,
003,

In the csv file, the number next to the topmost 002 is the sum of the Col2 where Col1
is 002 and the remaining field next to other 002 rows will be blank, the same process repeats for 003 and so on.

Would anyone please help me to make a T-SQL statement for the above requirement? I am using VB codes.

Thanks

k

1. Add an AutoNumber field named as ID to your table;

2. Create the query in Access:

SELECT t1.Col1, t2.sumCol2
FROM yourTable AS t1 LEFT JOIN [SELECT Col1, SUM(Col2) as sumCol2, min(ID) as minID
FROM yourTable Group By Col1]. AS t2 ON (t1.ID=t2.minID) AND (t1.Col1=t2.Col1);

3. Export this query to a csv file from Access.

|||

Thank you very much limno. Your T-SQL code perfectly solved my problem.

k
sql