Regarding indexes (I think)

I have an issue where i can do the following
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Now, those numbers should at up to the count of the top one,
I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
dbreindex with the same problem.
Anyone have any suggestions?Try,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
go
AMB
"Gary" wrote:

> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns 174,00
0
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>
>|||Correction,
DBCC UPDATEUSAGE ('db_name', 'table_name')
go
select count(*), count(case when ncolumn is not null then 1 end),
count(case when ncolumn is null then 1 end)
from table
go
AMB
"Alejandro Mesa" wrote:
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>|||Are there inserts into the table between each select?
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
> I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Thanks for the help, it sort of worked
Select count(*) from table and it returns 181,000
Select count(*) from table where ncolumn is not null and it returns 174,000
Select count(*) from table where ncolumn is null and it returns 153,000
Same results there, however,
select count(*), count(case when ncolumn is not null then 1 end),
count(ncolumn is null then 1 end)
from table
returns
181,000 159,000 22,000
which is correct,.but I'm still not sure whats going on there.
Another poster I saw asked if there were inserts happening during the
selects, and its not.
So, I'm still baffled. Something else to note, even if i run them
individually, instead of all 3 as a batch in QA, it still returns the wrong
count.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> Try,
> DBCC UPDATEUSAGE ('db_name', 'table_name')
> go
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> go
>
> AMB
> "Gary" wrote:
>
174,000|||If you are running parallel queries:
http://support.microsoft.com/kb/814509/en-us
http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
build).
S. L.
"Gary" <clgary@.yahoo.com> wrote in message
news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>I have an issue where i can do the following
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
> 174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Now, those numbers should at up to the count of the top one,
> I've ran dbcc checkdb, and it returns no errors. I've also tried dbcc
> dbreindex with the same problem.
> Anyone have any suggestions?
>|||Perhaps uncommitted inserts or updates are being held open by other
processes and your current transaction isolation level allows your query to
include "dirty reads". Sometimes a large number of updates (100,000s) can
take 1/2 hour or more to rollback if a batch transaction fails. Try running
the following, it will lock any records as it reads and will not include
"dirty reads".
Run sp_lock to see what other processes may have records locked. Also, if
the queries seems to take longer than usual to run using this locking
method, then you can use sp_who2 to determine if your SPID is blocked by
another process.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Select count(*) from table
Select count(*) from table where ncolumn is not null
Select count(*) from table where ncolumn is null
"Gary" <clgary@.yahoo.com> wrote in message
news:uDpR8ISGFHA.4004@.tk2msftngp13.phx.gbl...
> Thanks for the help, it sort of worked
> Select count(*) from table and it returns 181,000
> Select count(*) from table where ncolumn is not null and it returns
174,000
> Select count(*) from table where ncolumn is null and it returns 153,000
> Same results there, however,
> select count(*), count(case when ncolumn is not null then 1 end),
> count(ncolumn is null then 1 end)
> from table
> returns
> 181,000 159,000 22,000
> which is correct,.but I'm still not sure whats going on there.
> Another poster I saw asked if there were inserts happening during the
> selects, and its not.
> So, I'm still baffled. Something else to note, even if i run them
> individually, instead of all 3 as a batch in QA, it still returns the
wrong
> count.
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:61BE7216-D2F7-435D-B1E4-656E4D683C5E@.microsoft.com...
> 174,000
153,000
>|||Thanks,
That seems to do the trick
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OOqE3YSGFHA.2296@.TK2MSFTNGP15.phx.gbl...
> If you are running parallel queries:
> http://support.microsoft.com/kb/814509/en-us
> http://support.microsoft.com/default.aspx/kb/838166 (for getting the 878
> build).
> S. L.
> "Gary" <clgary@.yahoo.com> wrote in message
> news:unQeCtRGFHA.1188@.tk2msftngp13.phx.gbl...
>sql

Regarding filegroups

Dear all,
I was just wondering myself how could I do for to obtain to which filegroup
belong a specific table.
Imagine that you have primary, primary_history, secondary_history,
index_history, bla,bla
Any idea or though would be very appreciated.
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)What version of SQL Server? For SQL Server 2000, use the groupid column in s
ysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
> Dear all,
> I was just wondering myself how could I do for to obtain to which filegrou
p
> belong a specific table.
> Imagine that you have primary, primary_history, secondary_history,
> index_history, bla,bla
> Any idea or though would be very appreciated.
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)|||Hi Tibor,
It's ok. That's fine but I'm looking for user tables no indexes.
Where does sql stores that?
I'm seeing sysobjects table with xtype = 'u'
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:

> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||The physical aspects if a table is represented in sysindexes. Use WHERE indi
d IN (0,1). See Books
Online, sysindexes for more details about sysindexes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9EA44F73-094E-4458-8A0C-FF1BBD34123E@.microsoft.com...
> Hi Tibor,
> It's ok. That's fine but I'm looking for user tables no indexes.
> Where does sql stores that?
> I'm seeing sysobjects table with xtype = 'u'
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>|||I've seen how, using DMO...
Another possibility?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Tibor Karaszi" wrote:

> What version of SQL Server? For SQL Server 2000, use the groupid column in
sysindexes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:43476F7C-8EC3-4C59-B80B-DBF0E9E36B74@.microsoft.com...
>|||USE pubs
SELECT sfg.groupname
FROM sysfilegroups AS sfg
INNER JOIN sysindexes AS si
ON si.groupid = sfg.groupid
WHERE si.id = OBJECT_ID('dbo.authors')
AND indid IN(0,1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:9A0F784A-A179-4593-9C8A-1226B0941772@.microsoft.com...
> I've seen how, using DMO...
> Another possibility?
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Tibor Karaszi" wrote:
>

Regarding Dynamic query...

Hi,

I have basic design question regarding dynamic query,

When we have to build a dynamic query (which has table name also as an input parameter),

->Is it better to write a stored procedure ..?

or

->directly specify the dynamic query and set the command type as text in .NET code...?

I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..

Please through some light on this,

TIA

I don't think there's any right answer to this question. Personally I'd prefer to build a stored procedure for this because I like all of my database access code to be centrally located. Others will say it's a waste of time and effort to put this type of dynamic code into a stored procedure because it buys you nothing in terms of performance and it litters the database unnecessarily.

This is the type of topic that can generate a lot of debate. We'll see if anyone else bites. :-)

|||Why do you need to pass in a table name? Why does yourapplication have that much knowledge of your database? It soundsas though you have a serious architectural problem. You'vetightly coupled your application to your database. Figure out howto DECOUPLE the systems so that changing one won't break theother. One of the main benefits of stored procedures isencapsulation; passing in a table name defeats that goal.

sql

Regarding DTS

Hi,

I want to copy the data from Oracle table to excel file in DTS packages.
I have used transformation task, but it is asking for table but excel is a file.
please any body could tell em about this?

Thanks and Regards,
Purushotham,Use connection first.
In connection you will be able to find Excel files or Oracle.

Good Luck.

Regarding Data Transpose

Hello,
I have got a table, which consists 5 columns. If suppose there are ten
rows, then I want to insert the data present in this table into another
temporary table, which consists of 50 columns. So, effectively I want to
convert all the rows into one row by transposing. How can I do this?
--With Regards,
Sheshadrinath.R"Sheshadrinath R" <SheshadrinathR@.discussions.microsoft.com> wrote in
message news:37037772-7988-45E4-B957-62439844EFD8@.microsoft.com...
> Hello,
> I have got a table, which consists 5 columns. If suppose there are ten
> rows, then I want to insert the data present in this table into another
> temporary table, which consists of 50 columns. So, effectively I want to
> convert all the rows into one row by transposing. How can I do this?
> --With Regards,
> Sheshadrinath.R
Why? Have you considered just changing the way you display the data rather
than attempting such a thing in the database?
You haven't given enough information to answer your question fully. How
should we determine which rows get transposed to which columns for example?
Google this group for "transpose" and "crosstab" and you'll find plenty of
examples that might help you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Regarding Custom Code using table or list control(data set)

I have the report like this

list1

{

list2

{

table1

table header

table fields

table footer

}

}

now i want to calculate the sum of one field in table when it contains particular value using CUSTOM CODE ONLY

and i want to display it in table footer. please send me answer to this id as soon as possible.

Thanks

Sateesh Maduri

Maybe I'm missing something, but couldn't you just use a conditional aggregation expression like the following in the table footer:

=Sum(iif(Fields!A.Value = "abc", CInt(Fields!B.Value), 0))

-- Robert

|||

Hello Robert

Actually I already know that what u have given using sum().But I need Custom Code for that ,I mean if I have a dataset which fills the data in the table control How should I write Custom Code to calculate sum of one field when it contains particular value.

Thanks

sql

Regarding Custom Code

Hello

I have report like this

list1

list2(includes in list1)

table (includes in list2)

end of list2

end of list1

Now I want to calculate the sum of one column in table when it contains particular value and I want to display in table footer . I already know using sum() fuction.I want to know using custom code calculation for the above problem(How Can I repeat my data set in Custom code function for each row). So , If anyone knows Please reply.

It's Very Urgent

Thanks

Have a look at these

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=541699&SiteID=1

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com
> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default...b;en-us;302621
http://support.microsoft.com/default...b;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
--
Message posted via http://www.sqlmonster.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/default.aspx?scid=kb;en-us;302621
http://support.microsoft.com/default.aspx?scid=kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via SQLMonster.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.sqlmonster.com

Regarding BulkInsert

Hello,
I am trying to do this and i am getting this error..
create table #temptbl (rec varchar(284))
BULK INSERT #temptbl FROM 'z:\Testline.txt'
WITH ( DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r\n',
CODEPAGE = 'RAW',
MAXERRORS = 10000,
FIRSTROW = 2)
Server: Msg 8104, Level 16, State 2, Line 3
The current user is not the database or object owner of table '#timberline'.
Cannot perform SET operation.
User has BulkAdmin rights and he is a dbo in the database.
Pls advise.
Thanks,
Message posted via http://www.droptable.com> User has BulkAdmin rights and he is a dbo in the database.
This should work according to:
http://support.microsoft.com/defaul...kb;en-us;302621
http://support.microsoft.com/defaul...kb;en-us;243023
Are you certain the user is dbo or a db_owner role member? Please confirm
with SELECT USER, IS_MEMBER('db_owner') in the tempdb database. Tempdb is
recreated at startup so any permissions in that database are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"msqldba m via droptable.com" <u11604@.uwe> wrote in message
news:582f80513d042@.uwe...
> Hello,
> I am trying to do this and i am getting this error..
> create table #temptbl (rec varchar(284))
> BULK INSERT #temptbl FROM 'z:\Testline.txt'
> WITH ( DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r\n',
> CODEPAGE = 'RAW',
> MAXERRORS = 10000,
> FIRSTROW = 2)
> Server: Msg 8104, Level 16, State 2, Line 3
> The current user is not the database or object owner of table
> '#timberline'.
> Cannot perform SET operation.
>
> User has BulkAdmin rights and he is a dbo in the database.
> Pls advise.
> Thanks,
> --
> Message posted via http://www.droptable.com

Regarding auto increment of id in sql table

I currently working asp.net with c# and inserting data from webpage into sqlserver 2000 data table. I have an auto increment ID in a table. Let's say 10 are inserted. Then you delete those 10. You add another and the auto increment sets the id at 11. How can I get that table to start back to 0? Plz help me and give code for increment id programmatically using c#. Thanks in Adv,

if you execute Delete * from yourTbale then you will see what are seeing, Use Truncate instead of delete, eg, Truncate table yourTableName (this will delete all the rows from your table)

|||

I reset the autoincrement with this but I've never tried running it from a stored procedure.

DBCC CHECKIDENT(MyTempTable1,RESEED, 0)

You might have to go this route. "EXEC ('DBCC CHECKIDENT(MyTempTable1,RESEED,0)')"

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!

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: Table data changes auditing

Hi,

I have more than 1100 tables in my databse. From existing application data will insert into Database tables. I need to track the tables module wise when data inserting/Updating into these tables.

One way is i have to write the triggers for each table[1100 tables with auditing]. In my case this is not possible to write.

Is thesre any other way to find the updated,inserted tables when data is changing from application.

Regards

Hanu

hi,

You may get more help posting it in

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

SQL Server Database Engine forum

Although i maybe wrong

Good luck

Matt

reg error in sql

CREATE TABLE [dbo].[ContactInfo] (
[ContactID] [int] IDENTITY (1, 1) NOT NULL ,
[Title] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[JobTitle] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OfficePhone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomePhone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mobile] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OfficialEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PersonalEmail] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

In the above code i get the error"Line 3: Incorrect syntax near 'COLLATE'. " while compiling in sql

What is the error?

GO

i just ran the whole thing in my SQL Server (Sql Server 2000) and it worked fine.

hth,

mcm

|||

I ran this script in SQL server 2005 & it run without any problems :-) which version of SQL server are you using ?

Refreshing Tables In Database

I have a stored procedure that creates a table. It works fine except until
I
refresh the database, the table doesn't appear ... and views and reports
can't find it.
Does anyone know how I can do this automatically with SQL or VBA (I'm using
an Access project as my front end)?
Thanks!
HIf I had to guess, I would say that you create the table as one user and the
n
try to access it as another - just a guess
"Howard Brody" wrote:

> I have a stored procedure that creates a table. It works fine except unti
l I
> refresh the database, the table doesn't appear ... and views and reports
> can't find it.
> Does anyone know how I can do this automatically with SQL or VBA (I'm usin
g
> an Access project as my front end)?
> Thanks!
> H|||Howard Brody wrote:
> I have a stored procedure that creates a table. It works fine except
> until I refresh the database, the table doesn't appear ... and views
> and reports can't find it.
> Does anyone know how I can do this automatically with SQL or VBA (I'm
> using an Access project as my front end)?
> Thanks!
> H
This seems to be an Access related issue. I would post the question to
an Access group and see what they say.
David Gugick
Imceda Software
www.imceda.com|||I dunno ... the table doesn't appear until the database is refreshed whether
I run the code from a stored procedure or the query analyzer - which has
nothing to do the Access front end.
I would think that SQL would have a command or function for refreshing your
database. I just haven't found it yet.
H
"David Gugick" wrote:

> This seems to be an Access related issue. I would post the question to
> an Access group and see what they say.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Howard Brody wrote:
> I dunno ... the table doesn't appear until the database is refreshed
> whether I run the code from a stored procedure or the query analyzer
> - which has nothing to do the Access front end.
> I would think that SQL would have a command or function for
> refreshing your database. I just haven't found it yet.
> H
>
There's really no such thing as refreshing a database. Seeing the
objects in a list, for example, is a client issue (Access in this case
or could just as well be Query Analyzer). Once you create an object in
SQL Server, it's there, whether you see it in the user-interface of an
application or not. There's no real live-feed of database objects like
you have when viewing file, for instance, in Explorer - which keeps an
eye on folders for changes - most times. Even in QA, you don't need to
see the object in the Object Browser to run a query against it. Whereas,
I suspect, you need to see the object in Access to create a query or
open up the table in the Access UI. That was my reason for suggesting
you post to the Access group because possibly there is a feature of
Access that can mitigate this problem somewhat.
David Gugick
Imceda Software
www.imceda.com

Refreshing links in frontend

Hi everyone,
Could someone explain to me how to refresh the links to my sql server?
Each time i modify or create a field in a table, the modifications are
not implented in the front-end. How can i resolve this.
Greetings,
StefanieAre you selecting from views with SELECT *? In this case, check out
sp_refreshview in the Books Online.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Stefanie Pindew" <spindew@.yahoo.com> wrote in message
news:ehJkMpZXDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hi everyone,
> Could someone explain to me how to refresh the links to my sql server?
> Each time i modify or create a field in a table, the modifications are
> not implented in the front-end. How can i resolve this.
> Greetings,
> Stefanie
>|||Or are you referring to an Access front end? In that case,
you can use the Linked Table Manager or do it
programmatically with the refresh method of the tabledefs
collection.
--Sue
On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
<spindew@.yahoo.com> wrote:
>Hi everyone,
>Could someone explain to me how to refresh the links to my sql server?
>Each time i modify or create a field in a table, the modifications are
>not implented in the front-end. How can i resolve this.
>Greetings,
>Stefanie|||>--Original Message--
>Or are you referring to an Access front end? In that case,
>you can use the Linked Table Manager or do it
>programmatically with the refresh method of the tabledefs
>collection.
>--Sue
>On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
><spindew@.yahoo.com> wrote:
>>Hi everyone,
>>Could someone explain to me how to refresh the links to
my sql server?
>>Each time i modify or create a field in a table, the
modifications are
>>not implented in the front-end. How can i resolve this.
>>Greetings,
>>Stefanie
>.
>Hi,
I want it do it programmatically in the access front-end
and in the access adp front-end|||Stefanie,
I've just checked the following code and it works:
Dim tdLoop As TableDef
For Each tdLoop In CurrentDb.TableDefs
tdLoop.RefreshLink
Next
However, I can remember having problems when using the
.RefreshLink method... Give it a try though.
Regards,
Danny
"Stefanie Pindew" <spindew@.lollo.com> wrote in message news:02f201c35fee$71d670c0$a001280a@.phx.gbl...
> >--Original Message--
> >Or are you referring to an Access front end? In that case,
> >you can use the Linked Table Manager or do it
> >programmatically with the refresh method of the tabledefs
> >collection.
> >
> >--Sue
> >
> >On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
> ><spindew@.yahoo.com> wrote:
> >
> >>Hi everyone,
> >>
> >>Could someone explain to me how to refresh the links to
> my sql server?
> >>Each time i modify or create a field in a table, the
> modifications are
> >>not implented in the front-end. How can i resolve this.
> >>
> >>Greetings,
> >>
> >>Stefanie
> >
> >.
> >Hi,
> I want it do it programmatically in the access front-end
> and in the access adp front-end|||>--Original Message--
>Stefanie,
>I've just checked the following code and it works:
>Dim tdLoop As TableDef
>For Each tdLoop In CurrentDb.TableDefs
> tdLoop.RefreshLink
>Next
>However, I can remember having problems when using the
>..RefreshLink method... Give it a try though.
>Regards,
>Danny
>
>"Stefanie Pindew" <spindew@.lollo.com> wrote in message
news:02f201c35fee$71d670c0$a001280a@.phx.gbl...
>> >--Original Message--
>> >Or are you referring to an Access front end? In that
case,
>> >you can use the Linked Table Manager or do it
>> >programmatically with the refresh method of the
tabledefs
>> >collection.
>> >
>> >--Sue
>> >
>> >On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
>> ><spindew@.yahoo.com> wrote:
>> >
>> >>Hi everyone,
>> >>
>> >>Could someone explain to me how to refresh the links
to
>> my sql server?
>> >>Each time i modify or create a field in a table, the
>> modifications are
>> >>not implented in the front-end. How can i resolve
this.
>> >>
>> >>Greetings,
>> >>
>> >>Stefanie
>> >
>> >.
>> >Hi,
>> I want it do it programmatically in the access front-end
>> and in the access adp front-end
>
>.
>He thanks man, it works for me too|||Stefanie,
There are some issues with using .RefreshLink with Access97,
but I can't remember what they are. In case you have any
problems, I ended up implementing a solution that deleted the
linked tables from within Access and then re-linked them.
Both tasks can be accomplished using the DoCmd object:
DoCmd.DeleteObject
DoCmd.TransferDatabase
Email me if you want some sample code.
Regards,
Danny
"Stefanie Pindew" <spindew@.lollo.com> wrote in message news:066501c35ffe$995740b0$a301280a@.phx.gbl...
> >--Original Message--
> >Stefanie,
> >
> >I've just checked the following code and it works:
> >
> >Dim tdLoop As TableDef
> >For Each tdLoop In CurrentDb.TableDefs
> > tdLoop.RefreshLink
> >Next
> >
> >However, I can remember having problems when using the
> >..RefreshLink method... Give it a try though.
> >
> >Regards,
> >Danny
> >
> >
> >"Stefanie Pindew" <spindew@.lollo.com> wrote in message
> news:02f201c35fee$71d670c0$a001280a@.phx.gbl...
> >>
> >> >--Original Message--
> >> >Or are you referring to an Access front end? In that
> case,
> >> >you can use the Linked Table Manager or do it
> >> >programmatically with the refresh method of the
> tabledefs
> >> >collection.
> >> >
> >> >--Sue
> >> >
> >> >On Fri, 08 Aug 2003 12:46:17 +0200, Stefanie Pindew
> >> ><spindew@.yahoo.com> wrote:
> >> >
> >> >>Hi everyone,
> >> >>
> >> >>Could someone explain to me how to refresh the links
> to
> >> my sql server?
> >> >>Each time i modify or create a field in a table, the
> >> modifications are
> >> >>not implented in the front-end. How can i resolve
> this.
> >> >>
> >> >>Greetings,
> >> >>
> >> >>Stefanie
> >> >
> >> >.
> >> >Hi,
> >>
> >> I want it do it programmatically in the access front-end
> >> and in the access adp front-end
> >
> >
> >.
> >He thanks man, it works for me too

Refreshing data in GridView

Hi,

I’m new to SQL Express, but I have created a table and a stored proc to populate it.

I have dragged the table into a form so I can view the data in a GridView.

I have added a button to add new rows to the table.

All the above works fine, except when I hit add, the data gets added, but the GridView doesn’t update and show the new data. Is there some code I can add to the add button that also refreshed the GridView?

Thanks

Mike

You can use Response.Redirect(yourpage.aspx)|||There was a mistake above. That should be Response.Redirect("yourpage.aspx")sql

refresh SSIS pkg to get the change

Hi, all,

How do you refresh a SSIS pkg to get the latest table schema change?

I have this data flow task that will load data from a flat file into a table.

I got a Warning: Truncation may occur due to retrieving data from database column "txtSNumber" with a length of 50 to data flow column "txtSNumber" with a length of 20.

Then I went into Management Studio and changed the column size.
Now in my dev BID I got next:
[SQL Server 05[82]] Warning: The external metadata column collection is out of synchronization with the data source columns. The column "txt..." needs to be updated in the external metadata column collection.

I think this means my change on the table did not get into my ssis, and I could not find a way to refresh.

Thanks!It actually means the opposite. The change was detected by SSIS and it needs to update the metadata associated with the table in the data flow task (i.e. change the column width from 20 to 50). It does not automatically do this without some user interaction (hitting "OK") since you may need to know about the change and correct it if it is incorrect.|||

Thanks for the reply.

I am not looking for the automatic way to refresh if there is one.

I want to manually start the process in my design environment and click the OK as you have suggested, but I could not find out how or from where to make it happen.

I have tried right click on my .dtsx and select reload with upgrade, but that is not it. Where can tap into the megadata?

|||Look at the components in your data flow that have the yellow triangle with an exclamation mark in it. Start there by double clicking on that component to refresh the metadata.|||Double click on the yellow mark is the trick! Thanks!