Regarding a query

Hello,
I am facing a very complex problem in front of me. I dont know if its complex...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,
cspekWhat you are trying to achieve is called a cross-tab query (or a pivot table). I think you should be able to do something like this:

SELECT
 I.UnitName,
 CASE WHEN I.ID = 1 THEN MAX(I.Value) ELSE NULL END AS OS,
 CASE WHEN I.ID = 2 THEN MAX(I.Value) ELSE NULL END AS SP,
 CASE WHEN I.ID = 3 THEN MAX(I.Value) ELSE NULL END AS Drive
FROM
 InFotxt I
GROUP BY
 I.UnitName
ORDER BY
 I.UnitName

Terri|||I got the following error with the above select query

Server: Msg 8120, Level 16, State 1, Line 1
Column 'I.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

You can try the below statement,

select
unitName,
[Value] = (SELECT [Value] from InFotxt Where ID = 1 and s.unitName = unitName),
[Value] = (SELECT [Value] from InFotxt Where ID = 2 and s.unitName = unitName),
[Value] = (SELECT [Value] from InFotxt Where ID = 3 and s.unitName = unitName)
from (
select
UnitName
from InFotxt
group by UnitName
) S|||You must agggregate any columns not contained in the group by, ie:

MAX(CASE WHEN I.ID = 1 THEN MAX(I.Value) ELSE NULL END) as col1

sql

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.codecomments.com ***
sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>
|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>
sql

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.codecomments.com ***sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>

REG. RESTORE DATABASE FROM SQL 2005

I am facing problem while restoring database:
"An excepting occured while executing a Transact-SQL statement or batch.
Too many backup device specified for backup or restore; only 64 are
allowed
*** Sent via Developersdex http://www.developersdex.com ***sham,
Since BACKUP DATABASE only supports 64 backup devices, you should not need
more than 64 to restore. If you paste in your RESTORE command here, maybe
it will be apparent what is wrong.
RLF
"sham kulkarni" <sham252664@.gmail.com> wrote in message
news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>I am facing problem while restoring database:
> "An excepting occured while executing a Transact-SQL statement or batch.
> Too many backup device specified for backup or restore; only 64 are
> allowed
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Russel, I think the OP tries to restored sql server 2005 database on sql
server 2000 version
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
> sham,
> Since BACKUP DATABASE only supports 64 backup devices, you should not need
> more than 64 to restore. If you paste in your RESTORE command here, maybe
> it will be apparent what is wrong.
> RLF
> "sham kulkarni" <sham252664@.gmail.com> wrote in message
> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>>I am facing problem while restoring database:
>> "An excepting occured while executing a Transact-SQL statement or batch.
>> Too many backup device specified for backup or restore; only 64 are
>> allowed
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>|||Uri,
I did not think that of that possibility. Of course, if that is the case,
then it certainly will not work. But that is a strange message to get in
this case. I would have expected something like this:
Msg 3169, Level 16, State 1, Line 1
The backed-up database has on-disk structure version 611. The server
supports version 539 and cannot restore or upgrade this database.
RLF
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxQBlqFJIHA.4592@.TK2MSFTNGP02.phx.gbl...
> Russel, I think the OP tries to restored sql server 2005 database on sql
> server 2000 version
>
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:ujgOF1tIIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> sham,
>> Since BACKUP DATABASE only supports 64 backup devices, you should not
>> need more than 64 to restore. If you paste in your RESTORE command here,
>> maybe it will be apparent what is wrong.
>> RLF
>> "sham kulkarni" <sham252664@.gmail.com> wrote in message
>> news:uNS92ctIIHA.2480@.TK2MSFTNGP05.phx.gbl...
>>I am facing problem while restoring database:
>> "An excepting occured while executing a Transact-SQL statement or batch.
>> Too many backup device specified for backup or restore; only 64 are
>> allowed
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>>
>

reg Stored procedure Performance

Hi ,
I am facing a typical problem with Stored procedure performance. When i
excute my stored procedure first time it is taking around 60 seconds which i
s
not meeting expectations. when i excute the same sp second time with same
scenario it takes around 30 to 35 seconds. after this this behaviour is not
consistent. again if you ran sometimes it takes 50 seconds and sometimes it
takes 35 seconds like this. with this kind of behaviour i was not able to
figure it out my sp execution time exactly. my sp is having lot of dynamic
sql also.
Could anybody have any thoughts why the sp execution time is not consistent
for the same scenario.
Thanks in advance
BhaskarBhaskar wrote:
> Hi ,
> I am facing a typical problem with Stored procedure performance. When
> i excute my stored procedure first time it is taking around 60
> seconds which is not meeting expectations. when i excute the same sp
> second time with same scenario it takes around 30 to 35 seconds.
> after this this behaviour is not consistent. again if you ran
> sometimes it takes 50 seconds and sometimes it takes 35 seconds like
> this. with this kind of behaviour i was not able to figure it out my
> sp execution time exactly. my sp is having lot of dynamic sql also.
> Could anybody have any thoughts why the sp execution time is not
> consistent for the same scenario.
> Thanks in advance
> Bhaskar
SP duration should not be your primary source of performance tuning.
While it's important, it should follow examination of the execution
plans generated by the SP and the overall CPU consumed. Duration is
dependent on many factors like overall system CPU, lock contention,
physical vs. logical disk reads, etc. CPU, Reads, and Execution Plans
should be consistent across executions with the same parameters.
What you probably have is a poorly tuned procedure that is causing a lot
of reads, likely because of missing indexes or non-sargable expressions
in the queries. If that's the case, you'll see slower execution times
when SQL Server has to go to disk to read data as opposed to getting the
same from memory.
To properly tunes the query, examine the execution plan from Profiler or
Query Analyzer. You can use Profiler to see CPU, Duration, and Reads for
the individual statements and for the overall SP. 30 and 50 seconds are
both way too long for anything but a nightly batch process. You should
strive for times in the <100ms or better if possible.
If you see Table Scan or Clustered Index Scan operations, that's likely
the problem. Examine the query and see why indexes are not used. Could
be because there are no indexes available for the query to use or
because the expressions in the query are no optimizable.
For example:
WHERE LEFT(MyTable.MyCol, 1) = 'T'
is not optimizable even if an index exists on the MyCol column. Whereas,
WHERE MyTable.MyCol = 'T'
is optimizable when a MyCol index exists (still may not be used, but it
could be).
Post the DDL for your tables, indexes, and procedure if you need
specific help.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On Thu, 25 Aug 2005 21:17:02 -0700, "Bhaskar"
<Bhaskar@.discussions.microsoft.com> wrote:
>I am facing a typical problem with Stored procedure performance. When i
>excute my stored procedure first time it is taking around 60 seconds which
is
>not meeting expectations. when i excute the same sp second time with same
>scenario it takes around 30 to 35 seconds. after this this behaviour is no
t
>consistent. again if you ran sometimes it takes 50 seconds and sometimes i
t
>takes 35 seconds like this. with this kind of behaviour i was not able to
>figure it out my sp execution time exactly. my sp is having lot of dynamic
>sql also.
>Could anybody have any thoughts why the sp execution time is not consistent
>for the same scenario.
There are a lot of reasons.
The most obvious is if other people are using the server, which has
only so much horsepower to split between users. Are we talking a
server-class machine here, RAID5 for the data, separate disk for the
log, gigabytes of RAM, dual processors or better?
The slow first run is because (a) the SP needs to be compiled, because
(b) the plan is not already in cache, and (c) the data is not yet
cached, either. A few physical reads and your performance goes right
out the window.
As Dave suggests, the road to wisdom starts with running profiler,
looking at plans, looking at statistics - and getting away from
dynamic SQL! And OF COURSE, making certain you have the proper
indexes.
But when you tell me the time varies, that's a pretty strong sign that
other users are contending with your performance, and likely a sign
that your server is too busy or too small.
J.

Refresh problem in Crystal Viewer control

Dear Guru's
I am using Crystal viewer control in my VC++ application project. Here i am facing report refresh problem. If i call viewer control Refresh method directly the application is crashing. I am calling that inside OnPaint( ) .

The problem is the report data is not displaying first time the user has to click refresh button.

Please help me how to use "Refresh " method.

Thanks
arunNot sure if this will help, but make sure 'Save Data with Report' is NOT checked. Otherwise, have you tried calling Refresh as soon as the report is displayed (instead of waiting for the user to do it)?|||I am trying to refresh the report through code only using Viewer control 'Refresh' method.
If report displayed with blank then how user came to know that he has to refresh the report by clicking refresh button.

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
Thanks and Regards,
Prasanth
HI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:

> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth
|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
[vbcol=seagreen]
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
[vbcol=seagreen]
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:

Refering Local DB Tables from Master Procedures

Hi,
I am facing a problem while reading user database tables from a procedure in
the master database.
Example.
create procedure sp_Test
as
select count(*) from syscolumns
select count(*) from UserTable
go
I granted public access to this procedure in the master database.
When I execute the procedure in Query Analyzer with master DB, it is pulling
the record counts from the master tables.
When I execute it for some other user database, it returns the count of
records from syscolumn tables of that local database, but still takes the
count from UserTable of master database. It does not take the count from the
local database.
Could anyone help me know, if there is any setting, to point to the local
database instead of master while refering user tables (for system tables it
takes from local databases). Version : SQL Server 2000
--
Thanks and Regards,
PrasanthHI
USE master
GO
ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from ' + @.UserTable
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
USE pubs
GO
sp_test 'pubs', 'employee'
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Hi,
> I am facing a problem while reading user database tables from a procedure in
> the master database.
> Example.
> create procedure sp_Test
> as
> select count(*) from syscolumns
> select count(*) from UserTable
> go
> I granted public access to this procedure in the master database.
> When I execute the procedure in Query Analyzer with master DB, it is pulling
> the record counts from the master tables.
> When I execute it for some other user database, it returns the count of
> records from syscolumn tables of that local database, but still takes the
> count from UserTable of master database. It does not take the count from the
> local database.
> Could anyone help me know, if there is any setting, to point to the local
> database instead of master while refering user tables (for system tables it
> takes from local databases). Version : SQL Server 2000
> --
> Thanks and Regards,
> Prasanth|||Thanks for the reply Andras,
Currently I am using the procedure as you have given.
Is there any way I can directly query user tables just like system tables,
without passing the DB name and table names as parameters?
"Andras Jakus" wrote:
> HI
> USE master
> GO
> ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> as
> DECLARE @.strSQL as nvarchar(100)
> set @.strSQL = 'select count(*) from ' + @.UserTable
> select count(*) from syscolumns
> EXECUTE sp_executesql @.strSQL
> GO
> USE pubs
> GO
> sp_test 'pubs', 'employee'
> GO
> Andras Jakus MCDBA
> "Prasanth" wrote:
> > Hi,
> > I am facing a problem while reading user database tables from a procedure in
> > the master database.
> > Example.
> > create procedure sp_Test
> > as
> > select count(*) from syscolumns
> > select count(*) from UserTable
> > go
> >
> > I granted public access to this procedure in the master database.
> > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > the record counts from the master tables.
> > When I execute it for some other user database, it returns the count of
> > records from syscolumn tables of that local database, but still takes the
> > count from UserTable of master database. It does not take the count from the
> > local database.
> >
> > Could anyone help me know, if there is any setting, to point to the local
> > database instead of master while refering user tables (for system tables it
> > takes from local databases). Version : SQL Server 2000
> > --
> > Thanks and Regards,
> > Prasanth|||HI
Try this, but without parameter you can use with onli one table name.
(The db name parameter in first procedure unnecessary)
ALTER procedure sp_Test
as
DECLARE @.strSQL as nvarchar(100)
set @.strSQL = 'select count(*) from dbo.employee'
select count(*) from syscolumns
EXECUTE sp_executesql @.strSQL
GO
Andras Jakus MCDBA
"Prasanth" wrote:
> Thanks for the reply Andras,
> Currently I am using the procedure as you have given.
> Is there any way I can directly query user tables just like system tables,
> without passing the DB name and table names as parameters?
> "Andras Jakus" wrote:
> > HI
> > USE master
> > GO
> > ALTER procedure sp_Test @.dbname as sysname, @.UserTable as sysname
> > as
> >
> > DECLARE @.strSQL as nvarchar(100)
> >
> > set @.strSQL = 'select count(*) from ' + @.UserTable
> >
> > select count(*) from syscolumns
> > EXECUTE sp_executesql @.strSQL
> >
> > GO
> >
> > USE pubs
> > GO
> > sp_test 'pubs', 'employee'
> > GO
> >
> > Andras Jakus MCDBA
> >
> > "Prasanth" wrote:
> >
> > > Hi,
> > > I am facing a problem while reading user database tables from a procedure in
> > > the master database.
> > > Example.
> > > create procedure sp_Test
> > > as
> > > select count(*) from syscolumns
> > > select count(*) from UserTable
> > > go
> > >
> > > I granted public access to this procedure in the master database.
> > > When I execute the procedure in Query Analyzer with master DB, it is pulling
> > > the record counts from the master tables.
> > > When I execute it for some other user database, it returns the count of
> > > records from syscolumn tables of that local database, but still takes the
> > > count from UserTable of master database. It does not take the count from the
> > > local database.
> > >
> > > Could anyone help me know, if there is any setting, to point to the local
> > > database instead of master while refering user tables (for system tables it
> > > takes from local databases). Version : SQL Server 2000
> > > --
> > > Thanks and Regards,
> > > Prasanthsql

Reduce field data Length

Hi All,
I am facing some problem in the field data(Description field in Item table[table1]). In that table one field(Description field)data legth is 255 and type: nvarchar. In some operation this field data transfer to another table(Ex; table2) field. That table2 field length is 100. For some reason i can;t change the structure of the table. So allways i will get error data type mismatch or related error to data type. When i update table1 (Description field)field with null data and run the operation in database application i don;t get any error. The error causing because data length. I have 200,000 records in the table. I m planning to update the records (the description field) to reduce the length of data using sql command. If anyone know sql command for reducing the length of data(Not a field length-If its field i can restrict the length but my question is only reduce the length of data) please help me to resolve.

ThanksIt sounds like you dont want to run the ALTER TABLE but just want to chop the
contents of the field, if so you can even do this in the update query to table 2
but KISS would go like this

update table1
set longer_fieldie = substr(longer_fieldie, 1,100)

Ciao|||

Quote:

Originally Posted by tifoso

update table1 set longer_fieldie = substr(longer_fieldie, 1,100)
Ciao

By this way you are going to loose your data by chopping it to fit your size.|||

Quote:

Originally Posted by debasisdas

By this way you are going to loose your data by chopping it to fit your size.

i am getting error when i execute ur command
Here my code:
update IMA
set IMA_ITEMNAME = substr(IMA_ITEMNAME, 1,100)
WHERE IMA_itemid like N'316%'

and i m getting error:
'substr' is not a recognized function name.